问题描述:

How to get the column number from column name in Excel using Excel macro?

I think you want this?

**Column Name to Column Number**

```
Sub Sample()
ColName = "C"
Debug.Print Range(ColName & 1).Column
End Sub
```

**Edit**: Also including the reverse of what you want

**Column Number to Column Name**

```
Sub Sample()
ColNo = 3
Debug.Print Split(Cells(, ColNo).Address, "$")(1)
End Sub
```

**FOLLOW UP**

Like if i have salary field at the very top lets say at cell C(1,1) now if i alter the file and shift salary column to some other place say F(1,1) then i will have to modify the code so i want the code to check for Salary and find the column number and then do rest of the operations according to that column number.

In such a case I would recommend using `.FIND`

See this example below

```
Option Explicit
Sub Sample()
Dim strSearch As String
Dim aCell As Range
strSearch = "Salary"
Set aCell = Sheet1.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
MsgBox "Value Found in Cell " & aCell.Address & _
" and the Cell Column Number is " & aCell.Column
End If
End Sub
```

SNAPSHOT

While you were looking for a VBA solution, this was my top result on google when looking for a formula solution, so I'll add this for anyone who came here for that like I did:

Excel formula to return the **number from a column letter** (From @A. Klomp's comment above), where cell A1 holds your column letter(s):

=column(indirect(A1&"1"))

As the indirect function is volatile, it recalculates whenever any cell is changed, so if you have a lot of these it could slow down your workbook. Consider another solution, such as the 'code' function, which gives you the number for an ASCII character, starting with 'A' at 65. Note that to do this you would need to check how many digits are in the column name, and alter the result depending on 'A', 'BB', or 'CCC'.

Excel formula to return the **column letter from a number** (From this previous question How to convert a column number (eg. 127) into an excel column (eg. AA), answered by @Ian), where A1 holds your column number:

=substitute(address(1,A1,4),"1","")

Note that both of these methods work regardless of how many letters are in the column name.

Hope this helps someone else.

You could skip all this and just put your data in a table. Then refer to the table and header and it will be completely dynamic. I know this is from 3 years ago but someone may still find this useful.

Example code:

```
Activesheet.Range("TableName[ColumnName]").Copy
```

You can also use :

```
activesheet.listobjects("TableName[ColumnName]").Copy
```

You can even use this reference system in worksheet formulas as well. Its very dynamic.

Hope this helps!

Write and run the following code in the Immediate Window

```
?cells(,"type the column name here").column
```

For example `?cells(,"BYL").column`

will return 2014. The code is case-insensitive, hence you may write `?cells(,"byl").column`

and the output will still be the same.