Repeat Operation in Excel Macro

[Matt]

I am trying to write an Excel macro to perform some
manipulation on data. I have successfully written the
transformation for a single line but need to tell Excel
to repeat since if I enter multiple times within the
macro itself it is too large and displays a compiler
error. Ideally I would like the macro to repeat until it
reaches a blank cell, but alternatively I need to be able
to set it to repeat say 50 times.

Any help gratefully appreciated.

 

[Trevor Shuttleworth]

Matt

for i = 1 to 50
' your code
next i

you'll need to adjust cell references. For example:

Range("A1") would become Range("A" & i)

If you have contiguous data in column A, you can establish the last row as:

Dim LastRow as Long
LastRow = Range("A65536").End(xlUp).Row

and then use:

for i = 1 to LastRow

Regards

Trevor

 

[Matt]

Many thanks for your reply. However, I am not very used
to working with VB. Can you please expand more
specifically on how I define i in the macro?

 

[Trevor Shuttleworth]

Matt

a short example:

Sub MattTest()
Dim i As Long
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
MsgBox Range("A" & i)
' your code here ...
Next 'i
End Sub

Regards

Trevor