Consulting

Results 1 to 12 of 12

Thread: Match worksheet function in VBA searching on multiple criteria

  1. #1

    Match worksheet function in VBA searching on multiple criteria

    https://onedrive.live.com/view.aspx?...OsKMFNy3gfzCis

    I have a spreadsheet of currency trading data. (link to sample attached) I want to return the close value (F column) having found the correct Date (A column) and Time (B column).
    I have a cell formula which works in the Excel spreadsheet. =Index((F2:F34),Match(K2&K3,A2:A34&B2:B34),0)
    Where K2 and K3 are the date and time combination I am looking for.

    I would like to get this into vba so I can enter the Date and Time as variables.

    So far I cannot get this into vba. I have broken down the formula into component steps to see where the issue lies.

    Index works ok: Selection.Value = WorksheetFunction.Index(Range("F2:F34"), 19) 'returns the 19th value in the Range. So far so good.

    The Match function works well like this with only one column (Date) being searched.
    Selection.Value = WorksheetFunction.Match(Range("K2"), Range("A2:A34"), 0) 'returns the date searched for from a column of dates

    I however get a compile error when I try to search on two columns, namely Date and Time
    Selection.Value = WorksheetFunction.Match(Range("K2") & Range("K3"), Range("A2:A34") & Range("B2:B34"), 0)

    Is there a way to use the Match WorksheetFunction to search on multiple criteria in vba?

    Thank you.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Selection.Value = [Index((F2:F34),Match(K2&K3,A2:A34&B2:B34),0)]
    or
    Selection.Value = Evaluate("Index((F2:F34),Match(K2&K3,A2:A34&B2:B34),0)")
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Thank you very much p45cal I will give those a try.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    If this is part of a VBA application, then you could forego using WS formulas

    TestFuncClose () just returns the Close for a date and time

    TestFunc () returns a 4 element array for a date and time with the 4 parameters using a single search


    Sub drv()
    
    
        Dim A As Variant
        
        With ActiveSheet
            MsgBox TestFuncClose(.Cells(1, 1).CurrentRegion, .Range("K2"), .Range("K3"))
        
            A = TestFunc(.Cells(1, 1).CurrentRegion, .Range("K2"), .Range("K3"))
            MsgBox "Open = " & A(1)
            MsgBox "High = " & A(2)
            MsgBox "Low = " & A(3)
            MsgBox "Close = " & A(4)
        End With
    
    
    End Sub
    
    
    '3 = open, 4 = high, 5 = low, 6 = close
    Function TestFuncClose(Rin As Range, Din As Date, Tin As Date) As Variant
        Dim vData As Variant
        Dim i As Long
        
        TestFuncClose = CVErr(xlErrNA)
        
        vData = Rin.CurrentRegion.CurrentRegion
        
        For i = 2 To UBound(vData, 1)
            If vData(i, 1) = Din And vData(i, 2) = Tin Then
                TestFuncClose = vData(i, 6)
                Exit Function
            End If
        Next
    
    
    End Function
    
    
    Function TestFunc(Rin As Range, Din As Date, Tin As Date) As Variant
        Dim vData As Variant
        Dim i As Long
        Dim A(1 To 4) As Double
        
        TestFunc = CVErr(xlErrNA)
        
        vData = Rin.CurrentRegion.CurrentRegion
        
        For i = 2 To UBound(vData, 1)
            If vData(i, 1) = Din And vData(i, 2) = Tin Then
                A(1) = vData(i, 3)
                A(2) = vData(i, 4)
                A(3) = vData(i, 5)
                A(4) = vData(i, 6)
                TestFunc = A
                Exit Function
            End If
        Next
    
    
    End Function
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Hi p45Cal, I could not get either of those two suggested pieces of code to work. I have found something that seems to work which is:

    Sub Findclose()
    Range("K4").Select
    Range("K4").Value = WorksheetFunction.Index(Range("F2:F34"), WorksheetFunction.Match(Range("K2"), Range("A2:A34"), 0) + WorksheetFunction.Match(Range("K3"), Range("B2:B34"), 0) - 1)

    End Sub

    Why do I need the minus one at the end?
    If I run the code without it I get the close from the row below the close I am searching for. I understand the -1 is jumping back up a row to find the required data but not sure why the function moves down a row in the first place.

    If I do not put WorksheetFunction in front of Index and Match I get a function or sub not defined error.




  6. #6
    Thank you Paul for your effort and your post. I think I have found a single line of code answer which I posted about earlier. It seems pretty robust so far. I am wondering why given all the things Excel and VBA do very well why it is so difficult to essentially return data from a search based on two columns.

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Mellstock View Post
    I have found something that seems to work which is:
    Sub Findclose()
    Range("K4").Select
    Range("K4").Value = WorksheetFunction.Index(Range("F2:F34"), WorksheetFunction.Match(Range("K2"), Range("A2:A34"), 0) + WorksheetFunction.Match(Range("K3"), Range("B2:B34"), 0) - 1)
    End Sub
    This is nonsense.
    "Seems" is the operative word.
    Try running it in the file you linked to in msg#1 looking for 5:00 10 March 2020
    Also try looking for 5:00 9th March 2020 which isn't in the list - it returns a value!

    Addition after posting:
    Attached, that file with a button to run your line of code, and the other two I suggested earlier, and puts the 3 results into K4:M4.
    First search for 17:15 9th March 2020; all fine, three similar results in cells K4, L4 & M4.
    Second, search for 5:00 10 March 2020; Correct results in L4 and M4, your code throws an error.
    Now try 5:00 9th March 2020 for which there is no data in the table; L4 and M4 both display correcly #N/A, whereas your code return a value of 13055! (This is the close on the 10th March at 5am)
    Attached Files Attached Files
    Last edited by p45cal; 03-29-2020 at 05:44 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by Mellstock View Post
    Thank you Paul for your effort and your post. I think I have found a single line of code answer which I posted about earlier. It seems pretty robust so far. I am wondering why given all the things Excel and VBA do very well why it is so difficult to essentially return data from a search based on two columns.

    Sub Findclose()
    Range("K4").Select
    Range("K4").Value = WorksheetFunction.Index(Range("F2:F34"), WorksheetFunction.Match(Range("K2"), Range("A2:A34"), 0) + WorksheetFunction.Match(Range("K3"), Range("B2:B34"), 0) - 1)
    End Sub
    

    1. What do you plan to do when you have 35 rows of data?

    2. Don't need to select the cell to use it
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    Hi p45cal, thank you for your post and your demonstrations that your code works. I saw your post with the formulas late last night and had high hopes for using the expression with the [] brackets first thing this morning.
    I had to put CSE entered {} around the Excel spreadsheet version of the formula when working on a PC with Excel 2010 earlier this week so had seen this sort of thing before. No idea of course how it applies in VBA.
    I usually use a computer with Excel 365.

    I must have entered your formulae incorrectly for which I apologise.
    That you from keeping me from going down a time consuming blind alley.

  10. #10
    Hi Paul, I like to work with a small dataset before rolling out to a bigger one. When the time comes I would probably use something like or involving Cells(Rows.Count,1).End(xlUP).

    Yes I know I select Ranges and cells more often than I need to. I helps me demonstrate to myself that I am manipulating the spreadsheet the way I am wanting to.

    I also sometimes need to leave a cell selected in a particular place in order to run the next procedure to be called. Thank you for your interest,


    Ian

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    In VBA:

    Sub M_snb()
        y = CDate("2020/03/09 17:30:00")
        sn = Sheet1.Cells(1).CurrentRegion
        
        For j = 1 To UBound(sn)
          If sn(j, 1) + sn(j, 2) = y Then Exit For
        Next
        
        If j <= UBound(sn) Then MsgBox sn(j, 6)
    End Sub

  12. #12
    VBAX Newbie
    Joined
    Aug 2020
    Location
    Houston, Texas
    Posts
    2
    Location
    On a now closed thread, also dealing with a search on multiple criteria. mikerickson's posted (09-01-2011, 09:57 AM) code line as follows:

    CSEFormula = ("Match(" & Chr(34) & sTerm1 & Chr(34) & "&CHAR(5)&" & Chr(34) & sTerm2 & Chr(34) & "," & .Range("C1:C100").Address(, , , True) & "&CHAR(5)&" & .Range("E1:E100").Address(, , , True) & ",0)")

    I was able to get that code to work for me, but I don't understand what
    &CHAR(5)& does.

    What is the purpose of that code snippet and why is it necessary in the formula?
    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •