MATCH Value from Two Columns VBA

[dskribow]

Hi Everyone,

Here's what I have:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CustName[/TD]
[TD]CustID[/TD]
[TD]FName[/TD]
[TD]LName[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane Dooey[/TD]
[TD]1[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]John[/TD]
[TD]Dooey[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Jane[/TD]
[TD]Dooey[/TD]
[/TR]
</tbody>[/TABLE]


Here's what I'm trying to do: In VBA, get the CustID that matches the CustName.

Here's what I've got so far:

[COLOR=#333333]WorksheetFunction.Index(Sheet2.Range("b2:d5"), WorksheetFunction.Match(Sheet2.Range("a2"), Sheet2.Range("c2:c5") & " " & Sheet2.Range("d2:d5"), 0), 1)[/COLOR]

I've also tried to get this to work, to no avail:

[COLOR=#333333]WorksheetFunction.Index(Sheet2.Range("b2:d5"), WorksheetFunction.Match(Sheet2.Range("a2"), WoksheetFunction.Index(Sheet2.Range("c2:c5") & " " & Sheet2.Range("d2:d5"),) 0), 1)[/COLOR]

How can I do this/what am I doing wrong? I know that the worksheet version of the first code only works asan array formula in a worksheet, but the second one works just fine as a regular formula.


All advice is greatly appreciated.

Thanks.

 

[tonyyy]

You could try implementing a For Each / Next loop...

Sub CustID()

Dim r As Range
Dim LastRow As Long

LastRow = Cells(Rows.Count, "C").End(xlUp).Row
For Each r In Range("C2:C" & LastRow)
    If Range("A2").Value = r & " " & r.Offset(0, 1) Then MsgBox r.Offset(0, -1)
Next r

End Sub

Cheers,

tonyyy