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"
        sumstr = sumstr &  columberLetter & "1,"
    End If

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

Categorized in:


Last Update: March 4, 2024