I cannot seem to get this to work and i am hoping one of the resident experts can shed some light on what i am doing wrong.
Essentially, I am trying to use a combination of Index and Match functions from within VBA to identify multiple criteria and then return a related value from another column in the identified row. I have attached a sample file with the following 2 simple scenarios:
On a Userform are 2 scenarios:
Single Criteria [WORKING] - Selecting a date from the top combobox triggers the macro to match the selection with a column on the "Raw Data" sheet. It if there is a match it puts the corresponding value from column C ("Flag") into the top label on the userform.
Multiple Criteria [NOT WORKING] - Selecting a date from the bottom combobox triggers the macro to match the selection with a column on the "Raw Data" sheet. It also attempts to match a string (stock symbol) from the "Main" worksheet in A2 to a column on the "Raw Data" worksheet. The bottom label on the userform should only show a value if both criteria are met.
After spending some time on this and learning about the behavior of the Match function in VBA i think i have gotten very close... but i am still missing something. Hopefully it is syntax related. Any help would be greatly appreciated!
Single Criteria Code[Working]:
Private Sub combobox1_Change()
Dim TheDate As Date
Dim TheSymbol
Dim Match As Variant
Dim colSymbol As Range
Dim colDate As Range
Dim colFlag As Range
TheSymbol = Sheets("Main").Range("A2").Value
TheDate = Menu2.combobox1.Value
With Worksheets("Raw Data")
Set colSymbol = .Range("A2:A" & .Range("A65536").End(xlUp).row)
Set colDate = .Range("B2:B" & .Range("B65536").End(xlUp).row)
Set colFlag = .Range("C2:C" & .Range("C65536").End(xlUp).row)
End With
On Error Resume Next
'ONLY 1 CRITERIA & 1 CRITERIA RANGE
Match = Application.Match(CLng(TheDate), colDate, 0)
On Error GoTo 0
If IsError(Match) Then
msgbox "Not Found"
Else
Menu2.flag01.Caption = Format(Application.Index(colFlag, Match, 0), "X")
End If
End Sub
Multiple Criteria Code [Not Working]:
Private Sub ComboBox2_Change()
Dim TheDate As Date
Dim TheSymbol
Dim Match As Variant
Dim colSymbol As Range
Dim colDate As Range
Dim colFlag As Range
TheSymbol = Sheets("Main").Range("A2").Value
TheDate = Menu2.ComboBox2.Value
With Worksheets("Raw Data")
Set colSymbol = .Range("A2:A" & .Range("A65536").End(xlUp).row)
Set colDate = .Range("B2:B" & .Range("B65536").End(xlUp).row)
Set colFlag = .Range("C2:C" & .Range("C65536").End(xlUp).row)
End With
On Error Resume Next
'MULTIPLE CRITERIA & MULTIPLE CRITERIA RANGES
Match = Application.Match(CLng(TheDate) & CStr(TheSymbol), colDate & colSymbol, 0)
On Error GoTo 0
If IsError(Match) Then
msgbox "Not Found"
Else
Menu2.flag01.Caption = Format(Application.Index(colFlag, Match, 0), "X")
End If
End Sub
Bookmarks