Match worksheet function in VBA searching on multiple criteria

[Mellstock]

https://onedrive.live.com/view.aspx?resid=7C97F3C9E2ED9EE6!481&ithint=file%2cxlsx&wdLOR=c8C8FFA4D-EC57-4FF7-8FA6-E9FACCEAD9DC&authkey=!AOsKMFNy3gfzCis

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.

[p45cal]

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

[Mellstock]

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

[Paul_Hossler]

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

[Mellstock]

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.

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

[p45cal]

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)

[Paul_Hossler]

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

[Mellstock]

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.

[Mellstock]

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

[snb]

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

[trhenkel]

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.