VBA - MATCH function with 2 criterias

[lordbadsta]

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.

 

[texasalynn]

what is this?
Range("reportsCOB")
a range name? what does it define? are they true dates

 

[lordbadsta]

what is this?
Range("reportsCOB")
a range name? what does it define? are they true dates

Yes it defines a true date

but it is define as long in VBA therefore it works in the code (mat)

 

[texasalynn]

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?

 

[lordbadsta]

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#

 

[texasalynn]

it was defined just had a different date

 

[lordbadsta]

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

 

[texasalynn]

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

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)

 

[lordbadsta]

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

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

 

[texasalynn]

appreciate your post back