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
Convert to number in Excel with error checking If your cells display an error indicator (green triangle in the top left corner), converting text strings to numbers is a two-click thing: Select all the cells containing numbers formatted as text. Click the warning sign and select Convert to Number.
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))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With