When user inputs a column letter, convert that to number

[Jaymond Flurrie]

So plain and simply, I ask a user that what is the column. The user might answer A or IJ or XEE.

Is there a simple way to translate this to a number? Like A = 1, B = 2, XFD = 16384 and so on.

I would need the number in my VBA code to do for example like:

    Dim lastheaderrow As Integer
    lastheaderrow = controlsheet.Range("LastHeaderRow").Value

    Dim lastheadercolumn As Integer
    lastheadercolumn = 1
   
    Dim firstdatarangecell As Range
    Set firstdatarangecell = Cells(lastheaderrow + 1, lastheadercolumn + 1)

and obviously I can't count "A+1 = 2". The row part works just fine.

Edit. Actually, it doesn't make a lot of sense to have more than 26 (even that's way too many) headercolumns, so I think for this purpose

    Dim lastheadercolumnletter As String
    lastheadercolumnletter = controlsheet.Range("LastHeaderColumn").Value

    Dim lastheadercolumn As Integer
    lastheadercolumn = Asc(lastheadercolumnletter) - 64

does the trick.

 

[StephenCrump]

Perhaps? ColNo = Cells(1, Range("LastHeaderColumn").Value).Column

Although depending on what you're doing, you might be able to get VBA to determine the last column directly?

 

[johnnyL]

'Convert a Column Letter to a Column Number
   lastheadercolumn = Range(lastheadercolumnletter & 1).Column