Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping code in Excel macro
Hi to all,
Would appreciate assistance to do a loop for step2 below starting from the last row (last record row 4993) up the the first record. (My code is for 3 records only) A1:AU4993=StockBase A2:A4993=StockIndex A2:AU4993 has 199 records of 26 rows each The 1st row cols R:AU has the balance Brought Forward The 26th row cols R:AU has the closing balance Sub StockCarryForward() ' ' StockCarryForward Macro ' Macro recorded 2/24/2005 by RobertR ' ' With Application .Calculation = xlManual .MaxChange = 0.001 .CalculateBeforeSave = False End With ActiveWorkbook.PrecisionAsDisplayed = False For Each cell_in_loop In Range("HIROWS1") If cell_in_loop.Value = 0 Then With cell_in_loop .EntireRow.Hidden = True End With Range("R4993:AU4993").Select Selection.Copy Range("R4968").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("R4967:AU4967").Select Application.CutCopyMode = False Selection.Copy Range("R4942").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("R4941:AU4941").Select Application.CutCopyMode = False Selection.Copy Range("R4916").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.Goto Reference:="HIROWS10" Selection.EntireRow.Hidden = False Application.Goto Reference:="StockIndex" Application.CutCopyMode = False Selection.ClearContents End If Next End Sub The macro code does the following 1.hides rows 2 to 25 for each record (0 in HIROWS10) 2.copiespaste specialvalues from row 26 cols R:AU to row 1 cols R:AU (ie just the row above) 3.unhides all hidden rows 4.deletes Index numbers in A2:A4993 (which give various values to R:AU) Thank you and my apologies for length. -- RobertR |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping code in Excel macro
I am truly sorry to those who have been trying to help. The code is not
working. I am trying to correct it now. If someone can just help me with a looping code for this portion, explained below, I wiil surely appreciate their assitance. It is no longer necessary to hide any rows so long as the data in Cols R:AU is Copied and PastedSpecial Values to 25 rows higher Cols R:AU. ie. Copy R4993:AU4993 to R4968, similarly copy R4967:AU4967 to R4942:AU4942 upto the last record R27:AU27 copy to R2:AU2. Thank you. RobertR (to differenciate, there is another Robert) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping code in Excel macro
I have since achieved it by by the long way, created =formulas
for all the values to be copied then CopyPasteSpecialValuesSkipBlanks - the processes by macro. I think works much faster than looping. RobertR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macro Looping Helps | Excel Discussion (Misc queries) | |||
stopping code from looping | Excel Worksheet Functions | |||
Looping macros using VB code | Excel Discussion (Misc queries) | |||
Code looping when it should not | Excel Programming | |||
VBA code for looping through open workbooks and worksheets | Excel Programming |