As you already know, rows in Excel are labeled with numbers, so it’s easy to get to that row by specifying a number. But it’s not that easy to go, for example, to the 121st column because it’s labeled by one to three letters, so the first column is A and the last (16,384) is XFD.

In order to know, what label has the 121^{st} column, you have to convert 121 to a character label. You can achieve this by using an Excel formula.

1 |
=LEFT(ADDRESS(ROW(),A2,2),FIND("$",ADDRESS(ROW(),A2,2),1)-1) |

This formula will take the numerical value from **A2** and returns it as a column label. Let’s autofill this formula for the rest of the cells.

The formula works, but it’s quite complicated. Let’s create a function that we can call from inside the worksheet, which takes only one parameter.

This VBA function gets one parameter, which indicated the column number, and returns the label from **A**(1) to **XFD**(16,384). If the number is larger, it returns False.

1 2 3 4 5 6 7 8 9 10 11 12 |
Function NumbersToColumns(myCol As Integer) If myCol >= 1 And myCol <= 16384 Then iA = Int((myCol - 1) / 26) fA = Int(IIf(iA - 1 > 0, (iA - 1) / 26, 0)) NumbersToColumns = IIf(fA > 0, Chr(fA + 64), "") & _ IIf(iA - fA * 26 > 0, _ Chr(iA - fA * 26 + 64), "") & _ Chr(myCol - iA * 26 + 64) Else NumbersToColumns = FALSE End If End Function |

As you can see, the VBA function also works.