Home |
Search |
Today's Posts |
#1
|
|||
|
|||
getting a macro to run X amount of times..
Hey all, I have a macro that I need to run the amount of time equal to the last value in a list which will change in a monthly basis. I tried to get it with a for loop but I don't know how to get it to run a cetain amount of times, should I be using some kind of count and array or is there a very simple way of doing this. The macro is below if any one has any thoughts: Sub tesIncrement20() Range("D108:R108").Select Selection.Copy Windows("test").Activate Sheets("cal").Select Range("B1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Insert Shift:=xlDown Windows("North_central_agent.xls").Activate ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Reference").Select Windows("test").Activate Range("A1").Select Selection.Delete Shift:=xlUp Selection.Copy Windows("North_central_agent.xls").Activate Range("H103").Select ActiveSheet.Paste ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Summary").Select Windows("test").Activate Range("B1").Select Application.CutCopyMode = False Windows("North_central_agent.xls").Activate End Sub -- Hru48 ------------------------------------------------------------------------ Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895 View this thread: http://www.excelforum.com/showthread...hreadid=397177 |
#2
|
|||
|
|||
Just to make sure I understand: you have a list of data that changes
from month to month, and the last cell in that list contains an integer number that represents the number of loops your program requires. Or did I misunderstand, and instead you need your macro to run until it encounters the last value in the list, which changes from month to month. If it's the first scenario, you can declare an integer variable- I use K (short for Kounter), and another integer variable, maybe something like LoopNum. Assign the value in the last cell to LoopNum, and then start a For loop with this syntax: For K = 1 to LoopNum {your code here} Next K That's an idea, but it would be helpful to see a sample of your data. |
#3
|
|||
|
|||
Hru,
There may be more than one question here -- How to find the last cell of a list whose length changes, or how to get it to loop according to the value in that cell. If the cell in the last item of the list will not move, then do something like this: Dim i as long Dim LoopCount as long LoopCount = Range( last cell ) for i = 1 to LoopCount code goes here next i -- Earl Kiosterud www.smokeylake.com "Hru48" wrote in message ... Hey all, I have a macro that I need to run the amount of time equal to the last value in a list which will change in a monthly basis. I tried to get it with a for loop but I don't know how to get it to run a cetain amount of times, should I be using some kind of count and array or is there a very simple way of doing this. The macro is below if any one has any thoughts: Sub tesIncrement20() Range("D108:R108").Select Selection.Copy Windows("test").Activate Sheets("cal").Select Range("B1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Insert Shift:=xlDown Windows("North_central_agent.xls").Activate ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Reference").Select Windows("test").Activate Range("A1").Select Selection.Delete Shift:=xlUp Selection.Copy Windows("North_central_agent.xls").Activate Range("H103").Select ActiveSheet.Paste ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Summary").Select Windows("test").Activate Range("B1").Select Application.CutCopyMode = False Windows("North_central_agent.xls").Activate End Sub -- Hru48 ------------------------------------------------------------------------ Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895 View this thread: http://www.excelforum.com/showthread...hreadid=397177 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to find out how to multiply gross hours times amount of pay. | New Users to Excel | |||
How do I set a macro to run at specific times? | Excel Discussion (Misc queries) | |||
How do I find the amount of hours between times? | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Can you limit the amount of times a calc is performed on certain cells | Excel Discussion (Misc queries) |