I need to know how to do a vlookup and then pull all the data under it in the
field that is the same as the lookup. ie i am looking up ford and in the
lookup range there is more than 1 ford in a row? so lookup range is
sheet1a2:ac1040. ford is in cells a407 thur a597. i need to pull a407 thur
ac407 in row 1 of sheet2 i know the formula for that what i am having a hard
time with is row2 of sheet 2 needs to be filled with a408 thur ac408. can
some one help me with this?

Your description isn't very clear. The code below "may" work, but if not use
it as a model or be a little clearer what you are looking for.

Sub findford()
Set Lookuprange = Sheets("Sheet1").Range("a2:a1040")

Sh2RowCount = 1
For Each cell In Lookuprange
If cell = "ford" Then
With Sheets("Sheet1")
.Range(.Cells(cell.Row, "A"), _
.Cells(cell.Row, "AC")).Copy _
Destination:= _
Sheets("Sheet2").Cells(Sh2RowCount, "A")
Sh2RowCount = Sh2RowCount + 1
End With
End If
Next cell


End Sub


"lee willis" wrote:

I need to know how to do a vlookup and then pull all the data under it in the
field that is the same as the lookup. ie i am looking up ford and in the
lookup range there is more than 1 ford in a row? so lookup range is
sheet1a2:ac1040. ford is in cells a407 thur a597. i need to pull a407 thur
ac407 in row 1 of sheet2 i know the formula for that what i am having a hard
time with is row2 of sheet 2 needs to be filled with a408 thur ac408. can
some one help me with this?