• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. Krishnakumar

    2 if conditions (includes 2 true results & 2 false condition)

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

    Custom MsgBox icon like vbCritical

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

    Listing files names and hyperlinks in a spreadsheet.

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

    Need macro

    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 +...
  5. Krishnakumar

    Pulling part of the cell out of the cell

    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
  6. Krishnakumar

    A Macro For Merge Cells

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

    VBA - loop through a range of cells, delete rows that match a set of criterias

    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
  8. Krishnakumar

    VBA unique values

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

    Macro to disable a button if listbox contains no data.

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

    Creating one list out of three (just the values present on all three lists)

    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...
  11. Krishnakumar

    Excel Macro takes too much time to work

    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...
  12. Krishnakumar

    Solve this text retrieve query

    @ 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
  13. Krishnakumar

    Extracting / Calculating data from multiple worksheets into one Master Worksheet

    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;)...
  14. Krishnakumar

    Compile Questionnaires in one workbook

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

    Finding closest text

    @ 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...
  16. Krishnakumar

    Finding closest text

    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
  17. Krishnakumar

    Finding closest text

    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)))...
  18. Krishnakumar

    Compile Questionnaires in one workbook

    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
  19. Krishnakumar

    Insert formula at the time of running the macro

    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...
  20. Krishnakumar

    How to define the range based on the last used value

    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
  21. Krishnakumar

    Manual Calc v&#39;s Auto Calc

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

    Date and Time

    Hi try =int(a1) Kris
  23. Krishnakumar

    Query on sumif

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

    Filter

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

    Formatting using Macro

    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
Back
Top