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.

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)")

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

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

Code:

---

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

---

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.

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.

Quote:

---

Originally Posted by Mellstock

I have found something that seems to work which is:

Code:

---

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)

Quote:

---

Originally Posted by Mellstock

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.

---

Code:

---

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

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.

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

In VBA:

Code:

---

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

---

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.