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 121st 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.
