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
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
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):
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:
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.
You can also use :
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
?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.