Excel column number from column name

Question

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

User · Answer

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.

User · Answer

Here s a pure VBA solution because Excel can hold joined cells   Public Function GetIndexForColumn Column As String  As Long     Dim astrColumn      As String     Dim Result          As Long     Dim i               As Integer     Dim n               As Integer      Column   UCase Column      ReDim astrColumn Len Column  - 1      For i   0 To  Len Column  - 1          astrColumn i    Mid Column   i   1   1      Next     n   1     For i   UBound astrColumn  To 0 Step -1         Result    Result     Asc astrColumn i   - 64    n           n    n   26      Next     GetIndexForColumn   Result End Function   Basically  this function does the same as any Hex to Dec function  except that it only takes alphabetical chars  A   1  B   2        The rightmost char counts single  each char to the left counts 26 times the char right to it  which makes AA   27  1   26   AAA   703  1   26   676    The use of UCase   makes this function case-insensitive

User · Answer

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

User · Answer

Based on Anastasiya s answer  I think this is the shortest vba command   Option Explicit  Sub Sample       Dim sColumnLetter as String     Dim iColumnNumber as Integer      sColumnLetter    C      iColumnNumber   Columns sColumnLetter  Column      MsgBox  The column number is    amp  iColumnNumber End Sub   Caveat  The only condition for this code to work is that a worksheet is active  because Columns is equivalent to ActiveSheet Columns

User · Answer

I think you want this   Column Name to Column Number  Sub Sample       ColName    C      Debug Print Range ColName  amp  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    amp  aCell Address  amp              and the Cell Column Number is    amp  aCell Column     End If End Sub   SNAPSHOT

User · Answer

In my opinion the simpliest way to get column number is  Sub Sample       ColName   ActiveCell Column     MsgBox ColName End Sub

User · Answer

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 amp  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

[vba] Excel column number from column name

Examples related to vba

Examples related to excel