MATCH Value from Two Columns VBA

[dskribow]

Hi Everyone,

Here's what I have:

	A	B	C	D
1	CustName	CustID	FName	LName
2	Jane Dooey	1	Jane	Doe
3		2	John	Dooey
4		3	John	Doe
5		4	Jane	Dooey

<tbody>
</tbody>

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