On Sun, 3 Aug 2008 17:17:29 -0700 (PDT), LunaMoon
wrote:

Hi all,

I recorded a very long VBA macro in Excel and the index of ranges is
in letter format,

for example, "M14:M19", etc.

Now I am going to run this macro programmatically and automatically in
a for loop and expand it from the left to the right so I want to
change the "M" in the above example automatically.

But after 26 letters, there will be AA, AB, etc. which is really hard
to program.

Is there a way to adapt the recorded macro (by changing as little as
possible) to more than 26 letters.

I really want to change as little as possible because I spent lots of
time recording this macro and it is really long and it has reference
to addresses such as above everywhere... it's going to be a total mess
if I try to change too much of its addressing...

Thanks!

Are you looping through one column at a time, or are you really "expanding" the
range to the right, so that first you go through one column (M14:M19), then two
columns (M14:N19), etc?

To loop through one column at a time, use the Offset property:

==========================
Option Explicit
Sub foo()
Dim rAddr As Range
Dim i As Long
Set rAddr = Range("M14:M19")

For i = 1 To 30 'loop through 30 columns
Set rAddr = rAddr.Offset(0, 1)
'do whatever
'for example
Debug.Print rAddr.Address
Next i
End Sub
==========================

To "expand" the range so it is multiple columns, use the Resize property:

==========================
Option Explicit
Sub foo()
Dim rAddr As Range
Dim i As Long
Set rAddr = Range("M14:M19")

For i = 1 To 30 'expand from 1 to 30 columns
Set rAddr = rAddr.Resize(columnsize:=i)
'do whatever
'for example
Debug.Print rAddr.Address
Next i
End Sub
=======================
--ron