converting excel function to VBA

[alfordtp]

anyone have an idea how to convert a match statement to vba with multiple conditions? I have situations from where it has to match two criteria, to matching six criteria.

Any thoughts on how to do this?


=IF(AND(ISNUMBER(MATCH(1,($K$2:$K$393=Y2)*($O$2:$O$393=$D$1),0)),
'ISNUMBER(MATCH(1,($K$2:$K$393=Y2)*($O$2:$O$393=$G$1),0))),1,"")

Thanks!

 

[Domenic]

To place the formula in AA2, for example, try...

[font=Courier New]Range("AA2").FormulaArray = "=IF(ISNUMBER(MATCH(1,($K$2:$K$393=Y2)*($O$2:$O$393=$D$1),0)),1,"""")"
[/font]

To add another condition, try...

[font=Courier New]Range("AA2").FormulaArray = "=IF(ISNUMBER(MATCH(1,($K$2:$K$393=Y2)*($O$2:$O$393=$D$1)[COLOR="Red"]*($P$2:$P$393=Z2)[/COLOR],0)),1,"""")"
[/font]

Additional conditions can be added, as desired. Of course, the more conditions you have, the less efficient the formula is going to be. You could convert the formula results to values. Or, if the formulas need remain in place, a helper column could be used.

 

[alfordtp]

I found this example, but dont know how to make it have multiple conditions.

This is from the following:
http://www.mrexcel.com/forum/showthread.php?t=495131


If IsNumeric(Application.Match(Cells(i, "B"), Worksheets("EMP List").Range("A:A"), 0)) = True Then
If Application.VLookup(Cells(i, "B"), Worksheets("EMP List").Range("A:G"), 7, 0) = "" Then
Cells(i, "H").Value = "n/a"
Else: Cells(i, "H").Value = Application.VLookup(Cells(i, "B"), Worksheets("EMP List").Range("A:K"), 11, 0)
End If
End If

 

[Domenic]

I found this example, but dont know how to make it have multiple conditions.

This is from the following:
http://www.mrexcel.com/forum/showthread.php?t=495131


If IsNumeric(Application.Match(Cells(i, "B"), Worksheets("EMP List").Range("A:A"), 0)) = True Then
If Application.VLookup(Cells(i, "B"), Worksheets("EMP List").Range("A:G"), 7, 0) = "" Then
Cells(i, "H").Value = "n/a"
Else: Cells(i, "H").Value = Application.VLookup(Cells(i, "B"), Worksheets("EMP List").Range("A:K"), 11, 0)
End If
End If

I don't think you can adapt the above code to return the desired result. Have you tried the solution I offered?