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.
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.