getting a macro to run X amount of times..

[Hru48]

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

 

[Dave O]

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.

 

[Earl Kiosterud]

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