Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Simplify Code help
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 82
Default Simplify Code help
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.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Simplify Code help
Hi,

not tested but this should do it,

Sub LookupUpdateExisting()
Dim Cel As Range, c As Range
For x = 1 To 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(, x) = c.Offset(, x)
End If
Next
End With
Next
End Sub

Mike

"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.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Simplify Code help
You forget a dot in front of the Range function on the For Each line.

Also untested... I think this slight simplification will do the same as your
code (it eliminates a loop)...

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
c.Offset(, 1).Resize(1, 5).Copy Cel.Offset(, X)
End If
Next
End With
End Sub

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Hi,

not tested but this should do it,

Sub LookupUpdateExisting()
Dim Cel As Range, c As Range
For x = 1 To 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(, x) = c.Offset(, x)
End If
Next
End With
Next
End Sub

Mike

"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.


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 897
Default Simplify Code help
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.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Simplify Code help
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.


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 897
Default Simplify Code help
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 -

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default Simplify Code help
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.

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default 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.

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 82
Default Simplify Code help
Thanks for your quick response Jim. Thanks Rick and Mike and JP for your
input.
This works perfectly and so fast. I'm always amazed at the vast knowledge
that I glean from you experts. This is exactly what I was looking for. One
last question. How would I copy over formats as well? (ie bold font..)

Thanks again.
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Jim Thomlinson" wrote:

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.

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Simplify Code help
Thanks for your quick response Jim. Thanks Rick and Mike and JP for your
input.
This works perfectly and so fast. I'm always amazed at the vast knowledge
that I glean from you experts. This is exactly what I was looking for. One
last question. How would I copy over formats as well? (ie bold font..)

I guess you didn't actually try my code out then, did you?<g

The code I posted preserve the formatting of the cell.

--
Rick (MVP - Excel)

Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 82
Default Simplify Code help
Yes I did try your code at home. Not at work. The code worked but for some
reason format was not maintained. I'll try at work tomorrow.

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



"Rick Rothstein" wrote:

Thanks for your quick response Jim. Thanks Rick and Mike and JP for your
input.
This works perfectly and so fast. I'm always amazed at the vast knowledge
that I glean from you experts. This is exactly what I was looking for. One
last question. How would I copy over formats as well? (ie bold font..)

I guess you didn't actually try my code out then, did you?<g

The code I posted preserve the formatting of the cell.

--
Rick (MVP - Excel)


Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 82
Default Simplify Code help
My mistake Rick, I tried Jim's code. I hadn't realized they're different.
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Rick Rothstein" wrote:

Thanks for your quick response Jim. Thanks Rick and Mike and JP for your
input.
This works perfectly and so fast. I'm always amazed at the vast knowledge
that I glean from you experts. This is exactly what I was looking for. One
last question. How would I copy over formats as well? (ie bold font..)

I guess you didn't actually try my code out then, did you?<g

The code I posted preserve the formatting of the cell.

--
Rick (MVP - Excel)


Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 82
Default Simplify Code help
Rick, I just tried you code. Font formatting was maintained bur I lost column
and row formatting on the master sheet.

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



"Rick Rothstein" wrote:

Thanks for your quick response Jim. Thanks Rick and Mike and JP for your
input.
This works perfectly and so fast. I'm always amazed at the vast knowledge
that I glean from you experts. This is exactly what I was looking for. One
last question. How would I copy over formats as well? (ie bold font..)

I guess you didn't actually try my code out then, did you?<g

The code I posted preserve the formatting of the cell.

--
Rick (MVP - Excel)


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Simplify code Inkel Excel Worksheet Functions 3 March 27th 09 05:52 PM
Please help me simplify the given code Bob Phillips Excel Programming 6 November 13th 07 07:28 PM
Need to simplify code alexwren Excel Discussion (Misc queries) 7 August 15th 06 08:07 PM
Simplify Code Soniya[_4_] Excel Programming 3 August 9th 06 06:55 PM
Help to simplify code. Michael Beckinsale Excel Programming 0 September 2nd 03 10:26 AM


All times are GMT +1. The time now is 12:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement