Evaluate Match 3 Conditions

[miami2k]

Hi Everybody,
I've found similar threads but I couldn't find the solution.
I have a table with some info and a macro adding a new line with more info.
I need a code to search the info contained in the first three cells of the new line in the previous rows and return row number with the matching values.

This is my code at the moment but i get error 2015. I think this is due to the definitions of Rigamatch.

Riga is the row number of the new line.

Dim RigaMatch As Variant
Dim ReserachArea As Range
Set ReserachArea = ActiveSheet.Range(Cells(1, 1), Cells(Riga, 3))

FContratti.Cells(Riga, 1).Name = "Crit1"
FContratti.Cells(Riga, 2).Name = "Crit2"
FContratti.Cells(Riga, 3).Name = "Crit3"


MFormula = "MATCH(Crit1 & Crit2 & Crit3 &, ReserachArea, 0)"
RigaMatch = FContratti.Evaluate(MFormula)

Any suggestion?


Thank you

 

[mart37]

something like this:
MFormula = "MATCH(Crit1&Crit2&Crit3," & ReserachArea1.Address & "&" & ReserachArea2.Address & "&" & ReserachArea3.Address & ", 0)"

 

[miami2k]

Mart37, thank you but if I put your line in the code, I get runt time error 424 Object required.

 

[miami2k]

I've also noticed something:
if my code is
MFormula = "MATCH(Crit1 & Crit2 & Crit3 &,ReserachArea, 0)" I get error 2015
but if my code is
MFormula = "MATCH(Crit1 & Crit2 & Crit3,ReserachArea, 0)" I get error 2029


Any help?


Thank you

 

[NARAYANK991]

Hi ,

Have you posted all of your code ?

Riga is not being assigned any value prior to being used ; it will therefore contain the value 0 , and since row #0 does not exist , Excel will generate an error.

Secondly , a formula should start with the = (equal to) symbol to be recognized as a formula.

 

[mart37]

i have used three ranges!
Set ReserachArea1 = ActiveSheet.Range(Cells(1, 1), Cells(riga, 1))
Set ReserachArea2 = ActiveSheet.Range(Cells(1, 2), Cells(riga, 2))
Set ReserachArea3 = ActiveSheet.Range(Cells(1, 3), Cells(riga, 3))

when you use evaluate the formula must be the same as an excel formula and not a vba-formula.

 

[miami2k]

Ok guys, I'm understanding the code now but still no success. I recreated the part of the code in a different file so you can help me.

Sub Macro1()'
' Macro1 Macro
'


Dim name As Variant
Dim lastneme As Variant
Dim profession As Variant
Dim result As Variant
Dim matchformula As String
Dim ReserachArea1 As Range
Dim ReserachArea2 As Range
Dim ReserachArea3 As Range




name = Range("A20").Value
lastname = Range("B20").Value
profession = Range("C20").Value


Set ReserachArea1 = Range("A2:A11")
Set ReserachArea2 = Range("B2:B11")
Set ReserachArea3 = Range("C2:C11")


ReserachArea1.Select
ReserachArea2.Select
ReserachArea3.Select


Cells(20, 1).name = "Crit1"
Cells(20, 2).name = "Crit2"
Cells(20, 3).name = "Crit3"


matchformula = "MATCH(Crit1&Crit2&Crit3," & ReserachArea1.Address & "&" & ReserachArea2.Address & "&" & ReserachArea3.Address & ", 0)"""


result = Application.Evaluate(matchformula)


Cells(22, 1) = result




End Sub