VBA - MATCH function with 2 criterias

lordbadsta

New Member
Joined
Apr 28, 2011
Messages
5
Hello

I have the following table made of 3 columns where I need to find the value of the 3rd column based on 2 variable in columns 1 and 2 in VBA

column A - column B - Column C
26/04/2011 - amount - 150
26/04/2011 - cash - 23
26/04/2011 - balance - 195
27/04/2011 - amount - 150
27/04/2011 - cash - 23
27/04/2011 - balance - 195


my code is the following

Sub testMATCH()

Dim mat, mat2, mat3, mat4 As Variant
Dim Rdate As Long
Dim lib As String

Rdate = Range("reportsCOB")
lib = "balance"

'this code works
mat = Application.Match(Rdate, Range("A:A"), 0)
mat2 = Application.Match(lib, Range("B:B"), 0)

'this code does not work ->runtime error 13 type mismatch
mat3 = Application.Match(Rdate & lib, Range("A:A") & Range("B:B"), 1)
mat4 = Application.Match(1, (Rdate = Range("A:A")) * (lib = Range("B:B")), 1)

End Sub

whereas the MATCH function based on 2 criterias works OK in excel, I can not make it work in VBA.

Your help is welcome.

PS: I do not wish to concatenate the 2 first columns in 1 for some reasons.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I have been trying to test your code but since you didn't answer all my questions, I am unable to help you at this time. In my test with a single cell defined with a true date I get an error on the code that you say works

the Rdate what does it define?
 
Upvote 0
I have been trying to test your code but since you didn't answer all my questions, I am unable to help you at this time. In my test with a single cell defined with a true date I get an error on the code that you say works

the Rdate what does it define?

range("reportsCOB") indeed refered to a named range which is a true date

however since if is not defined in your excel sheet


replace
Rdate = range("reportCOB")

with
Rdate = #4/27/2011#
 
Upvote 0
SOLVED

Sub testMATCH7()

Dim mat, mat2, mat3 As Variant
Dim rdate As Long
Dim lib As String

rdate = Range("reportsCOB")
lib = "balance"

'SINGLE CRITERIA
mat = Application.Match(rdate, Range("A:A"), 0)
mat2 = Application.Match(lib, Range("B:B"), 0)

'DOUBLE CRITERIA
mat3 = Evaluate("MATCH(1,(reportscob=A:A)*(""balance""=B:B),0)")

End Sub

mat returns 5
mat2 returns 4
mat3 returns 7


it took me the entire afternoon to get there..........

now I just have to integrate mat3 into an INDEX function to get my 3rd column value defined in VBA

SO GOOD

thx for your attention Texasalynn
 
Upvote 0
ok in doing a little research I found out the VBA does not like array so you have to create the variable as the formula would be entered and then do an evaluate. This seemed to work

Code:
mat3 = "MATCH(" & Rdate & "&""" & lib & """,$A$2:$A$10 & $B$2:$B$10,0)"
Result = Evaluate(mat3)
mat4 = "Match(1, (" & Rdate & " = $A$2:$A$10) * (""" & lib & """ = $B$2:$B$10), 1)"
vResult = Evaluate(mat4)
 
Upvote 0
ok in doing a little research I found out the VBA does not like array so you have to create the variable as the formula would be entered and then do an evaluate. This seemed to work

Code:
mat3 = "MATCH(" & Rdate & "&""" & lib & """,$A$2:$A$10 & $B$2:$B$10,0)"
Result = Evaluate(mat3)
mat4 = "Match(1, (" & Rdate & " = $A$2:$A$10) * (""" & lib & """ = $B$2:$B$10), 1)"
vResult = Evaluate(mat4)

which gives further options to my own code:
mat3 = Evaluate("MATCH(1,(reportscob=A:A)*(""balance""=B:B),0)")

and that is always good.

Nevertheless, I m impressed by your reactivity

Thanks
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top