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

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

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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
>

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


"[email protected]" 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
>
>