Thanks, knew I'd forgotten something.

--JP

On Sep 22, 2:52*pm, Mike H wrote:
hi,

You would need to initialise i (i=1) so it isn't zero on the first pass and
increment i outside the with - end with statement

Mike



"JP" wrote:
This is air code so test first.

Sub LookupUpdateExisting()
* * Dim Cel As Range, c As Range
* * Dim i As Long
* * Do Until i = 5
* * With Sheets("Master")
* * * * For Each Cel In Range(.Cells(2, 1), .Cells(.Rows.Count,
1).End(xlUp))
* * * * * * Set c = Sheets("Update").Columns(1).Find(Cel,
lookat:=xlWhole)
* * * * * * If Not c Is Nothing Then
* * * * * * * * Cel.Offset(, i) = c.Offset(, i)
* * * * * * End If
* * * * * * i = i + 1
* * * * Next
* * End With
* * Loop
End Sub

--JP

On Sep 22, 2:16 pm, DavidH56
wrote:
Hi,

Is there a way to simplyfy this code? I'm basically updating the Master
sheet in 5 adjacent columns.

Sub LookupUpdateExisting()
* * Dim Cel As Range, c As Range
* * With Sheets("Master")
* * * * For Each Cel In Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
* * * * * * Set c = Sheets("Update").Columns(1).Find(Cel, lookat:=xlWhole)
* * * * * * If Not c Is Nothing Then
* * * * * * * * Cel.Offset(, 1) = c.Offset(, 1)
* * * * * * End If
* * * * Next
* * End With
* * With Sheets("Master")
* * * * For Each Cel In Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
* * * * * * Set c = Sheets("Update").Columns(1).Find(Cel, lookat:=xlWhole)
* * * * * * If Not c Is Nothing Then
* * * * * * * * Cel.Offset(, 2) = c.Offset(, 2)
* * * * * * End If
* * * * Next
* * End With
* * With Sheets("Master")
* * * * For Each Cel In Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
* * * * * * Set c = Sheets("Update").Columns(1).Find(Cel, lookat:=xlWhole)
* * * * * * If Not c Is Nothing Then
* * * * * * * * Cel.Offset(, 3) = c.Offset(, 3)
* * * * * * End If
* * * * Next
* * End With
* * With Sheets("Master")
* * * * For Each Cel In Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
* * * * * * Set c = Sheets("Update").Columns(1).Find(Cel, lookat:=xlWhole)
* * * * * * If Not c Is Nothing Then
* * * * * * * * Cel.Offset(, 4) = c.Offset(, 4)
* * * * * * End If
* * * * Next
* * End With
* * With Sheets("Master")
* * * * For Each Cel In Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
* * * * * * Set c = Sheets("Update").Columns(1).Find(Cel, lookat:=xlWhole)
* * * * * * If Not c Is Nothing Then
* * * * * * * * Cel.Offset(, 5) = c.Offset(, 5)
* * * * * * End If
* * * * Next
* * End With
End Sub

Thanks in advance.
--
By persisting in your path, though you forfeit the little, you gain the
great.- Hide quoted text -

- Show quoted text -