Find row with matching cell and retrieve values (VBA)

[John]

Using VBA, say I am in Sheet1 worksheet. In sheet2 is four columns.
I have a value stored in a variable. I want to find the row that has
the matching value in Sheet2 Column D. Once it finds the row that
matches, copy the values out of that row's A, B, and C cells into
variables. Any help on this would be much appreciated.

Thanks.

JR

 

[Tom Ogilvy]

Dim vVal, vValA, vValB, vValC
vVal = worksheets("Sheet1").Range("A1").Value
res = Application.Match(vVal,Worksheets("Sheet2").Range("D1500"),0)
if not iserror(res) then
With Worksheets("Sheet2")
vValA = .range("A1:A500)(res).Value
vValB = .range("B1:B500)(res).Value
vValC = .range("C1:C500)(res).Value
end if