Converting column letter to number

Question

I found code to convert number to column letter.

How can I convert from column letter to number?

Sub colLtr()
Dim mycolumn
mycolumn = 1000
Mcl = Left(Cells(1, mycolumn).Address(1, 0), InStr(1, Cells(1, mycolumn).Address(1, 0), "$") - 1)
MsgBox Mcl
End Sub

Answer 1

You can reference columns by their letter like this:

Columns("A")

So to get the column number, just modify the above code like this:

Columns("A").Column

The above line returns an integer (1 in this case).

So if you were using the variable mycolumn to store and reference column numbers, you could set the value this way:

mycolumn = Sheets("Sheet1").Columns("A").Column

And then you could reference your variable this way:

Sheets("Sheet1").Columns(mycolumn)

or to reference a cell (A1):

Sheets("Sheet1").Cells(1,mycolumn)

or to reference a range of cells (A1:A10)you could use:

Sheets("Sheet1").Range(Cells(1,mycolumn),Cells(10,mycolumn))