Using Match in VBA to find the row number based on multiple criteria

[BBxcl]

Hi,

I’m working on a table in Excel VBA and I want to assign the row number of the cell that meets multiple criteria to a variable.

Normally, using Excel’s Match function, I would do something like =MATCH(1, (criteria logic 1)*(criteria logic 2), 0)

However when I try to do this in excel VBA using Evaluate, I get a type mismatch error. The following is a snippet of my VBA code:

Dim tbl As ListObject
Dim result_rw As Variant
Dim match_Formula As String

Set tbl = Sheet1.ListObjects("Table_1")

match_Formula = "MATCH(1,(" & """ & tbl.DataBodyRange(rw,3) & """ & "=" & """ & tbl.ListColumns(3).DataBodyRange & """ & ")*(" & """ & tbl.ListColumns(10).DataBodyRange & """ & "=1), 0)"
result_rw = Evaluate(match_Formula)

Where the variable rw has been calculated through previous calculations and stores the value of the row number of the data that I am comparing to.

The type mismatch error occurs when I try to assign the String to match_Formula variable and I suppose this is happening because I am trying to store a Range inside a String variable. However, I have to perform my comparison against the whole column range so I'm not sure what other way there is around this.

Can anyone please help with this?

 

[offthelip]

You might find it worth while looking at this thread where I helped somebody with an index/match problem which i solved using the dictionary object which can be much faster than using index match.I don't understand what you are trying to do so , I can't help with specific code at this time:
Speed up excel file (maybe VBA?)