Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro change problem
This Macro uses a Column "E" of unique numbers in Archive sheet for any
changes in Current sheet and if it finds any overwites the data in the corresponding rows on Archive sheet. Both sheets currently have 31 columns. I need to remove the B column from both sheets which means the unique numbers in Column E will now be Column D and both sheets down to 30 columns. I changed references to "E" in the script to "D" and the Resize values to 30, but it does'nt work, I get an error message and when I debug the highlited line is : Dn1.Offset(, -4).Resize(, 30).Copy Dn2.Offset(, -4).Resize(, 30) anybody see where i'm going wrong and set me on the right track Many thanks for your help this is the original without the changes I made Sub synchronize() Dim Rng1 As Range, Dn1 As Range, Rng2 As Range, Dn2 As Range With Sheets("Current") '1 Set Rng1 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp)) End With With Sheets("Archive") '2 Set Rng2 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp)) End With For Each Dn2 In Rng2 For Each Dn1 In Rng1 If UCase(Trim(Dn2)) = UCase(Trim(Dn1)) Then Dn1.Offset(, -4).Resize(, 31).Copy Dn2.Offset(, -4).Resize(, 31) End If Next Dn1 Next Dn2 End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro change problem
Change the , -4).Resize to ,-3).Resize in both instances of that statement.
When you changed E to D that caused "Current column number minus 4" to equate to zero, and there is no column zero. "Dr Hackenbush" wrote: This Macro uses a Column "E" of unique numbers in Archive sheet for any changes in Current sheet and if it finds any overwites the data in the corresponding rows on Archive sheet. Both sheets currently have 31 columns. I need to remove the B column from both sheets which means the unique numbers in Column E will now be Column D and both sheets down to 30 columns. I changed references to "E" in the script to "D" and the Resize values to 30, but it does'nt work, I get an error message and when I debug the highlited line is : Dn1.Offset(, -4).Resize(, 30).Copy Dn2.Offset(, -4).Resize(, 30) anybody see where i'm going wrong and set me on the right track Many thanks for your help this is the original without the changes I made Sub synchronize() Dim Rng1 As Range, Dn1 As Range, Rng2 As Range, Dn2 As Range With Sheets("Current") '1 Set Rng1 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp)) End With With Sheets("Archive") '2 Set Rng2 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp)) End With For Each Dn2 In Rng2 For Each Dn1 In Rng1 If UCase(Trim(Dn2)) = UCase(Trim(Dn1)) Then Dn1.Offset(, -4).Resize(, 31).Copy Dn2.Offset(, -4).Resize(, 31) End If Next Dn1 Next Dn2 End Sub . |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro change problem
That did it, i should have picked up on that.
Thanks very much for your help "JLatham" wrote in message ... Change the , -4).Resize to ,-3).Resize in both instances of that statement. When you changed E to D that caused "Current column number minus 4" to equate to zero, and there is no column zero. "Dr Hackenbush" wrote: This Macro uses a Column "E" of unique numbers in Archive sheet for any changes in Current sheet and if it finds any overwites the data in the corresponding rows on Archive sheet. Both sheets currently have 31 columns. I need to remove the B column from both sheets which means the unique numbers in Column E will now be Column D and both sheets down to 30 columns. I changed references to "E" in the script to "D" and the Resize values to 30, but it does'nt work, I get an error message and when I debug the highlited line is : Dn1.Offset(, -4).Resize(, 30).Copy Dn2.Offset(, -4).Resize(, 30) anybody see where i'm going wrong and set me on the right track Many thanks for your help this is the original without the changes I made Sub synchronize() Dim Rng1 As Range, Dn1 As Range, Rng2 As Range, Dn2 As Range With Sheets("Current") '1 Set Rng1 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp)) End With With Sheets("Archive") '2 Set Rng2 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp)) End With For Each Dn2 In Rng2 For Each Dn1 In Rng1 If UCase(Trim(Dn2)) = UCase(Trim(Dn1)) Then Dn1.Offset(, -4).Resize(, 31).Copy Dn2.Offset(, -4).Resize(, 31) End If Next Dn1 Next Dn2 End Sub . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro change problem
Just glad I could help.
"Dr Hackenbush" wrote: That did it, i should have picked up on that. Thanks very much for your help "JLatham" wrote in message ... Change the , -4).Resize to ,-3).Resize in both instances of that statement. When you changed E to D that caused "Current column number minus 4" to equate to zero, and there is no column zero. "Dr Hackenbush" wrote: This Macro uses a Column "E" of unique numbers in Archive sheet for any changes in Current sheet and if it finds any overwites the data in the corresponding rows on Archive sheet. Both sheets currently have 31 columns. I need to remove the B column from both sheets which means the unique numbers in Column E will now be Column D and both sheets down to 30 columns. I changed references to "E" in the script to "D" and the Resize values to 30, but it does'nt work, I get an error message and when I debug the highlited line is : Dn1.Offset(, -4).Resize(, 30).Copy Dn2.Offset(, -4).Resize(, 30) anybody see where i'm going wrong and set me on the right track Many thanks for your help this is the original without the changes I made Sub synchronize() Dim Rng1 As Range, Dn1 As Range, Rng2 As Range, Dn2 As Range With Sheets("Current") '1 Set Rng1 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp)) End With With Sheets("Archive") '2 Set Rng2 = .Range(.Range("E1"), .Range("E" & Rows.Count).End(xlUp)) End With For Each Dn2 In Rng2 For Each Dn1 In Rng1 If UCase(Trim(Dn2)) = UCase(Trim(Dn1)) Then Dn1.Offset(, -4).Resize(, 31).Copy Dn2.Offset(, -4).Resize(, 31) End If Next Dn1 Next Dn2 End Sub . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert row at change macro - how to change it. | Excel Discussion (Misc queries) | |||
macro that will change the font of a cell if i change a value | Excel Discussion (Misc queries) | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
How do I change macro text with another macro? | Excel Discussion (Misc queries) |