Need a vb loop for vlookup
 

I need a VB loop which will looks at ID's in cells A2 to A400, check to
see if corresponing ID's exist in cells I2:I400. If so, pick up Column
M and paste this back into Col G. If the ID does not exist then it
should say something like "New". I have got this far (gleamed and
amended from other Newsgroups postings) but I am unable to work out how
to do the loop.
Can somebody please help?

Many thanks
Tony

Sub CheckTotals()
With Worksheets("Totals")
.Cells(2, 7) = Application.VLookup(.Cells(2, 1), .Range("I2:M400"),
5, False)
End With
End Sub

Need a vb loop for vlookup
 

Suggest going to vba help index and look for FIND or FINDNEXT

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
I need a VB loop which will looks at ID's in cells A2 to A400, check to
see if corresponing ID's exist in cells I2:I400. If so, pick up Column
M and paste this back into Col G. If the ID does not exist then it
should say something like "New". I have got this far (gleamed and
amended from other Newsgroups postings) but I am unable to work out how
to do the loop.
Can somebody please help?

Many thanks
Tony

Sub CheckTotals()
With Worksheets("Totals")
.Cells(2, 7) = Application.VLookup(.Cells(2, 1), .Range("I2:M400"),
5, False)
End With
End Sub

Need a vb loop for vlookup
 

You are nearly there.

Sub CheckTotals()
Dim i%
With Worksheets("Totals")
For i = 2 to 400
.Cells(i, 7) = Application.WorksheetFunction.VLookup(.Cells(i, 1),
..Range("I2:M400"),
5, False)
Next i
End With
End Sub

By the way you can do the same thing through a simple formula entered in
G2:G400

Alok


" wrote:

I need a VB loop which will looks at ID's in cells A2 to A400, check to
see if corresponing ID's exist in cells I2:I400. If so, pick up Column
M and paste this back into Col G. If the ID does not exist then it
should say something like "New". I have got this far (gleamed and
amended from other Newsgroups postings) but I am unable to work out how
to do the loop.
Can somebody please help?

Many thanks
Tony

Sub CheckTotals()
With Worksheets("Totals")
.Cells(2, 7) = Application.VLookup(.Cells(2, 1), .Range("I2:M400"),
5, False)
End With
End Sub