Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range with a loop needed
Ok this is going to be long to explain.
Spreed Sheet has 7 columns and an unknown amount of rows as a total so columns from A to G and Rows = unknown Colum definition A = Date B = Account # C = Type of Account D = Amount posted (on our system) E = Amount posted (on an external system) F = Date when it close G = Total now there is several different groups that use the same set up in the same sheet What I want to do is to make a macro that copy the value from E to G as follow E.value = G.Value The problem I'm having is that I do not have a specific range for each of this group I can add or delete rows as I see fit, but the macro needs to keep working no matter what. my answer was, I named the very first cell of the group (G1_First) using the named range option in excel and the very last in that group named it (G1_Last). The macro I came up with was: (Group 1) For I = Range("G1_First").rows + 1 to Range("G1_Last").rows - 1 Cells(I,7).value = Cells(I,5).value Next I after doing this macro i went to the sheet and set it to run automaticaly in the code of that sheet. one macro per group 5 in total. the problem I'm facing is that the loop seens to be very slow, even though, the amount of rows are less than 1000. Can anyone come up with a better code for this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range with a loop needed
When I do this kind of stuff, I can usually depend on another column to have
data in it when that row is used -- for you, maybe the date or account number??? If that's ok with you, you could use something like this that actually does a copy|paste special|values: Option Explicit Sub testme() Dim LastRow As Long Dim myRng As Range Dim wks As Worksheet Set wks = ActiveSheet With wks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set myRng = .Range("E2:E" & LastRow) myRng.Copy .Range("G2").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End With End Sub I started in row 2 (E2 and G2) -- I figured headers in row 1. Change them if you need to. Alfredo wrote: Ok this is going to be long to explain. Spreed Sheet has 7 columns and an unknown amount of rows as a total so columns from A to G and Rows = unknown Colum definition A = Date B = Account # C = Type of Account D = Amount posted (on our system) E = Amount posted (on an external system) F = Date when it close G = Total now there is several different groups that use the same set up in the same sheet What I want to do is to make a macro that copy the value from E to G as follow E.value = G.Value The problem I'm having is that I do not have a specific range for each of this group I can add or delete rows as I see fit, but the macro needs to keep working no matter what. my answer was, I named the very first cell of the group (G1_First) using the named range option in excel and the very last in that group named it (G1_Last). The macro I came up with was: (Group 1) For I = Range("G1_First").rows + 1 to Range("G1_Last").rows - 1 Cells(I,7).value = Cells(I,5).value Next I after doing this macro i went to the sheet and set it to run automaticaly in the code of that sheet. one macro per group 5 in total. the problem I'm facing is that the loop seens to be very slow, even though, the amount of rows are less than 1000. Can anyone come up with a better code for this? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range with a loop needed
Hi Alfredo
To me it looks like you made a small typo, try this: For i = Range("G1_F").Row + 1 To Range("G1_L").Row - 1 Cells(i, 7).Value = Cells(i, 5).Value Next i HTH, Wouter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop variable range | Excel Programming | |||
loop variable range | Excel Programming | |||
loop variable range | Excel Programming | |||
loop variable range | Excel Programming | |||
Loop thru Range Help needed | Excel Programming |