|Our Nosy Dog, Alex|
Visual Basic for Applications, embedded in the Microsoft Office Suite, is one of the most unknown power tools available for office automation. (Check out a primer on Excel VBA here)
One of the most powerful aspects is the ability to loop through contents and perform an action on every row or column in a range.
Some functions in Excel, however, doesn't use the column number as an identifier. Instead, they use the normal spreadsheet nomenclature of column letter plus row number. So, the upper-left cell in a spreadsheet, row 1, column 1, is called "A1".
If you are trying to loop through columns using a for loop, this will cause you a bit of a headache.
I found a solution that is elegant!
Function Letter(num as integer) as string
letter =split(Cells(1, num ).Address, "$")(1)
Function Letter(byref ws as Worksheet, byval Num as Integer) as String
Dim a() As String = Split(CType(ws.Cells(1, Input), Range).Address, "$")