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

Hi

I've not seen application.vlookup before, so I assume it's in
reference I've not got switched on.

If it works anything like the worksheet function vlookup, then you'l
need to put the column ID in the third parameter.

If your range goes from I to M, column I is 1, column J is 2 and so o
until column M which would be 5.

So your function would read:
Application.Vlookup(.cells(r,c), .range("I2:M400"), 5)

I'm not 100% sure that it will work, but it's somewhere to start...

G

--
grinning_cro
-----------------------------------------------------------------------
grinning_crow's Profile: http://www.excelforum.com/member.php...nfo&userid=651
View this thread: http://www.excelforum.com/showthread.php?threadid=39862

Thanks to all for your replies - I am very grateful.
Alok's reply interests me - if it easier to enter it using a simple
formula then I would consider it. Being a bit of a newbie to Excel VBA
I thought the solution was to do it via VBA but it looks like I have
taken a long cut for a short cut!
If someone can supply the forumla solution (or give me advice on how it
could be achieved) I would also be very grateful

Best Regards
Tony

You really should consider using findnext instead. From HELP
FindNext Method
See Also Applies To Example Specifics
Continues a search that was begun with the Find method. Finds the next cell
that matches those same conditions and returns a Range object that
represents that cell. Doesn't affect the selection or the active cell.

expression.FindNext(After)

expression Required. An expression that returns a Range object.

After Optional Variant. The cell after which you want to search. This
corresponds to the position of the active cell when a search is done from
the user interface. Note that After must be a single cell in the range.
Remember that the search begins after this cell; the specified cell isn't
searched until the method wraps back around to this cell. If this argument
isn't specified, the search starts after the cell in the upper-left corner
of the range.

Remarks
When the search reaches the end of the specified search range, it wraps
around to the beginning of the range. To stop a search when this wraparound
occurs, save the address of the first found cell, and then test each
successive found-cell address against this saved address.

Example
This example finds all cells in the range A1:A500 that contain the value 2
and changes their values to 5.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Thanks to all for your replies - I am very grateful.
Alok's reply interests me - if it easier to enter it using a simple
formula then I would consider it. Being a bit of a newbie to Excel VBA
I thought the solution was to do it via VBA but it looks like I have
taken a long cut for a short cut!
If someone can supply the forumla solution (or give me advice on how it
could be achieved) I would also be very grateful

Best Regards
Tony