Vba cell address to column number in Excel

This topic, as the name implies, deals with finding column letters from the column number.

This sort of requirement arises, when a data is given and its location has to be found and used in the program.

for example, let's say the data is a string, which is present in Cell F8.

We can find the column number using a For loop.Now that, we have the column number.

Now, to use this address in the program, it has to be converted to a named address location.

In multi-module programming, this requirement often arises.

The following example demonstrates this:

  1. Sub Col_Letter()
  2. Dim vArr
  3. Dim col As String
  4. col = InputBox("Enter a Column number")
  5. vArr = Split(Cells(1, CLng(col)).Address(True, False), "$")
  6. MsgBox vArr(0)
  7. End Sub

The above example obtains a column number from the user and returns the corresponding Column letter.

The Screenshot of the VBA Editor is as shown below:

excel vba cell address to column number

The Output is as shown:

excel vba cell address to column number

excel vba cell address to column number

 

You can find similar Excel Questions and Answer hereunder

1) How can I get input from user through a prompt and assign user's input to a cell?

2) How can I convert Column numbers into Column names for use in range definition?

3) How to read a value from a cell in vba in Excel

4) Can I determine the number of times a character appears in a cell using formula?

5) I have to retrieve data from a specific column in a Table. In Excel, how can I do that?

6) How to see to which cells a cell is connected or used by. How to see the precedents of a cell

7) How can I avoid updates to cell values during macro execution?

8) How can I enter a text in a cell with subscript and superscript?

9) How can I update a listbox based on data in a list using VBA?

10) How can I ensure that user enters only certain acceptable values in an input cell?

 

Here the previous and next chapter