Search results

=IF(A2="active",IF(TODAY()-C2>180,"Confirmed","Pending"),IF(F2-C2>180,"Confirmed - Inactive","Pending - Inactive"))

Have a look at : http://www.h3.dion.ne.jp/~sakatsu/ktMsgBox_Series_English.htm

If you would like to have a formula solution, see: http://www.excelfox.com/forum/f13/list-file-names-using-formula-1463/

Hi Try Option Explicit Dim NextTick As Date Sub StartRefresh() GetRefreshed End Sub Private Sub GetRefreshed() Dim rngFormula As Range Set rngFormula = Sheet1.Range("c3") '<<< adjust the range rngFormula.Calculate NextTick = Now +...

Try something like in Access SELECT MID(MyField,IIF(StrConv(LEFT(MyField,1),1)="Z",2,1),InStrRev([MyField],"-")-IIF(StrConv(LEFT(MyField,1),1)="Z",2,1)) AS MyNewField FROM MyTable

Hi Try this code. To assign short cut key, hit Alt+F8 > Options and assign the required key. Sub MergeSelection() If TypeOf Selection Is Range Then Application.DisplayAlerts = False Selection.Merge Application.DisplayAlerts = True End If End Sub

You can even avoid the first loop. Dim ar ar = Sheet2.Range(&#34;a2&#34;, Sheet2.Range(&#34;a&#34; & Sheet2.Rows.Count).End(xlUp)) ar = Application.Transpose(ar) ar = Split(Join(ar, &#34;,&#34;), &#34;,&#34;) Kris

Or you may find this function useful. http://www.excelfox.com/forum/f12/extract-unique-values-range-69/ Kris

Me.BTN_MoveSelectedLeft.Enabled = Me.ModulesListBox.ListCount &#60;&#62; 0 Kris

Hi Try this code. Sub kTest() Dim d1 As Object, d2 As Object Dim i As Long, k, c As Long k = Range(&#34;a2:c80000&#34;).Value2 &#39;adjust the range Set d1 = CreateObject(&#34;scripting.dictionary&#34;) Set d2 = CreateObject(&#34;scripting.dictionary&#34;) For c = 1 To UBound(k...

Hi Try to avoid Select, Activate etc. Dim proper variables. See if this helps you. Public Sub UpdateGraph() Dim wksLTOK As Worksheet Dim wksCP As Worksheet Dim LastRow As Long Dim s As String Dim rngToCopy As Range Dim lngCalc As Long With Application...

@ shri ROWS($A$1:A1),ROWS($A$1:A1) should be ROWS($G$5:G5),ROWS($G$5:G5)) otherwise if I delete Col A, it gives you REF error, if I insert a row between row 2 and 5, the results will be incorrect. Kris

Hi Try this. Adjust the sheet names Sub kTest() Dim ka, i As Long, n As Long, dic As Object &#39;//IDs ka = Worksheets(&#34;Sheet1&#34;).Range(&#34;a1&#34;).CurrentRegion.Resize(, 3).Value2 &#39;&#60;&#60;== adjust the sheet name Set dic = CreateObject(&#34;scripting.dictionary&#34;)...

Glad I could help you and thanks for the feedback. Add the fields on the master sheet. Kris

@ Sajan, I do agree that Narayan did a great to explain the formula. What I'm saying that it doesn't give me maint4 from the following list maint1 , maint4, maint7 , maint10 , maint49 , vaint4 , vaint9 and the search key being maint5 I think the closest match would be maint4 from this list...

Since the OP is looking for closest match and if have a list like maint1 , maint4, maint7 , maint10 , maint49 , vaint4 , vaint9 and the expecting answer is maint4, only my formula would work. Kris

Hi There may be better ways =INDEX(B1:B5,MATCH(MIN(ABS(REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$A$1&&#34;0123456789&#34;))-1,&#34;&#34;)+ 0-IFERROR(SUBSTITUTE($B$1:$B$5,LEFT($A$1, MIN(FIND({0,1,2,3,4,5,6,7,8,9},$A$1&&#34;0123456789&#34;))-1),&#34;&#34;)+0,9.999E+307)))...

Hi Replace this line .Range("a" & .Rows.Count).End(3).Offset(1).Resize(n, dic.Count) = k with .Range("a2").Resize(n, dic.Count) = k Kris

Hi try Sub kTest() Dim r As Long r = Range(&#34;a&#34; & Rows.Count).End(3).Row With Range(&#34;b2:b&#34; & r) .Value = Date .Offset(, 1).FormulaR1C1 = &#34;=rc[-1]-rc[-2]&#34; .Offset(, 2).FormulaR1C1 = &#34;=(rc[-1]&#62;7)+0&#34; &#39;I gues you are referring column c rather than...

I don't think you need a loop to find the last non empty cell of a particular row cells(row_no,columns.count).end(xltoleft).column would give you the last non empty cell of row_no Kris

Hi I think you must open the other workbooks in new instance. Kris

Hi try =int(a1) Kris

Create a pivot table and Sort the sales by Ascending order. Kris

Hi Select 'Contains' and the criteria "*" (without quotes) for Numbers, 'Does not contain' and * Kris

Hi I think this line Range(FoundCell.Offset(1, -1), FoundCell.Offset(1, -1).End(xlDown)).EntireRow.Delete should be Range(FoundCell.Offset(1), FoundCell.Offset(1).End(xlDown)).EntireRow.Delete Kris