How do I change the row/column format in a macro to beyond letters?

[LunaMoon]

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!

 

[Proof Of Concept]

This function will convert an integer to a column letter, so if you are
looping through columns, say 1...100 you can do the conversion that way:

' e.g. GetColLetter(100)
Function GetColLetter(ByVal iCol As Integer) As String
Dim strAddr As String
Dim iPos As Integer
strAddr = Mid(ThisWorkbook.Worksheets(1).Cells(1, iCol).Address, 2)
iPos = InStr(strAddr, "$")
GetColLetter = Left$(strAddr, iPos - 1)
End Function

Or modify the range directly

Range("M14:M19")
becomes
Range(Cells(14,"M"),Cells(19,"M"))
which is the equivalent of
Range(Cells(14,13),Cells(19,13))

e.g. to loop through 100 columns

Dim y as Integer, rng As Range
For y = 1 to 100
Set rng = Range(Cells(14,x),Cells(19,x))
Next


--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility
Now with Table Compare for quick table comparisons

 

[Rick Rothstein \(MVP - VB\)]

Let VB do all the hard work...

Addr = "G5:CD10"
FirstColumn = Range(Left(Addr, InStr(Addr, ":") - 1)).Column
SecondColumn = Range(Mid(Addr, InStr(Addr, ":") + 1)).Column

Rick

 

[Ron Rosenfeld]

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