Simplify Code help
Ooops... In the resize of the range change the 4 to a 5...
--
HTH...
Jim Thomlinson
"Jim Thomlinson" wrote:
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(0, 1).resize(1,4).value = c.Offset(0,
1).resize(1,4).value
End If
Next
End With
end sub
--
HTH...
Jim Thomlinson
"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.
|