How to Get Column Letter from Number Using an Excel VBA Macro

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