When dynamically creating formulas using an Excel VBA macro, being able to switch between the column letter and numbers in a loop is very important.
For example, let’s say you are looping over a number of columns in a Excel VBA macro. In the example, below, it’s possible I want to create a dynamic sum of the first row based on an input, and store that in a formula.
My program could be structured as follows:
Sub CreateSum() Dim colNumber as integer Dim input as integer Dim sumstr as string input = 5 For colNumber = 1 To input columnLetter = Split(Cells(1, colNumber).Address, "$")(1) If colNumber = input Then sumstr = sumstr & columnLetter & "1" Else sumstr = sumstr & columberLetter & "1," End If Next ThisWorkbook.Sheets("Example").Cells(1,input+1).Formula = "=SUM(" & sumstr & ")" End Sub
With one line of code, we can easily obtain the column letter given a number for the column.
columnLetter = Split(Cells(1, colNumber).Address, "$")(1)
If you would like to go the other way, from column letter to column number, you would want the following:
columnNumber = Range(columnLetter & 1).Column