Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How do I change the row/column format in a macro to beyond letters?
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! |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How do I change the row/column format in a macro to beyond letters?
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 "LunaMoon" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How do I change the row/column format in a macro to beyond letters?
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 "LunaMoon" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How do I change the row/column format in a macro to beyond letters?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the row/column format in a macro to beyond letters? | Excel Discussion (Misc queries) | |||
change column numbering to column letters in my spreadsheet view | Excel Discussion (Misc queries) | |||
how to change column of letters? | Excel Discussion (Misc queries) | |||
How do change a column of data in capitol letters to small letters | Excel Discussion (Misc queries) | |||
How can I change column numbers back to column letters? | Excel Worksheet Functions |