Variable in macro loop

[paget]

I need to run a FOR NEXT loop in a macro for a specific number of times depending upon the maximum number in a column of a worksheet. Can this be achieved to avoid me having to manually edit the VBA every time I want to run the macro?

 

[FormR]

Hi, welcome to the forum!

You could try something like this.

Sub Example()
Dim i As Long
For i = 1 To Application.Max(Sheets("Sheet1").Columns("A"))
    'do stuff
Next i
End Sub

 

[paget]

Thank you for this and please excuse my lack of understanding.
If I use the dimension statement and then want to perform a calculation to get the variable i, can I not just refer to the range where the data to be tested resides? For instance, the loop needs to run a number of times defined by the integer value of 1000000 divided by the maximum number in the range 'seacalc'.
I tried:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]For i = 1 To Int(1000000 / Application.Max(seacalc))
but that did not work.
[/FONT]

 

[FormR]

the range 'seacalc'.

Hi, what is 'seacalc' - is it a named range in the workbook? Or a variable defined and set in your code?

 

[paget]

A named range in the workbook

 

[FormR]

OK - try like this:

For i = 1 To Int(1000000 / Application.Max(Range("seacalc")))

 

[paget]

Brilliant; thank you so much.