Recent content by Krishnakumar

=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...