My variable found_match will return "A7:A45" when I use debug.print, this range can change depending on other factors, but currently it returns this.
I have a Match function set up which currently works if I type in "A7:A45" as it's range, but if I replace it with the variable it doesn't work.
row_num3 = Evaluate("MATCH(1,('" & ws1.Name & "'!A7:A45=""" & condition_1 & """)*('" & ws1.Name & "'!B7:B45=""" & condition_2 & """)*('" & ws1.Name & "'!F7:F45=""" & condition_3 & """),0)")
Debug.Print row_num3
The above works, but if I replace the range with the variable it returns an error when I use debug.print:
row_num3 = Evaluate("MATCH(1,('" & ws1.Name & "'found_match=""" & condition_1 & """)*('" & ws1.Name & "'found_match2=""" & condition_2 & """)*('" & ws1.Name & "'found_match3=""" & condition_3 & """),0)")
Debug.Print row_num3
This code is running on ws3 (worksheet 3), but it's looking for it's match on ws1 (worksheet)
How can I format the Match function to work with variables as the range rather than specifically typing the range (eg. A7:A45)
Thanks!
found_matchis a variable, not a named range, you need to concatenate it in:"MATCH(1,('" & ws1.Name & "'" & found_match & "="""– Roryrow_num3 = Evaluate("MATCH(1,('" & ws1.Name & found_match & "=condition_1"")*('" & ws1.Name & found_match2 & "=condition_2"")*('" & ws1.Name & found_match3 & "=condition_3"")I think I have still formatted this incorrectly EDIT: found_match is a variable. I find the concatenation super confusing – Samfound_match? Is it a range variable or string variable storing a string address? – Scott Cranerfound_match = "A" & row_num2 & ":A" & lastrow3(row_num2 and lastrow3 return a number) EDIT: I haven't set a Dim for it – Sam& ws1.Name & "'!" & found_match– Scott Craner