Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Converting column letter to number

Tags:

excel

vba

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
like image 624
user1902849 Avatar asked Nov 09 '13 15:11

user1902849


People also ask

How do I change column text to number in Excel?

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.


1 Answers

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))
like image 76
ARich Avatar answered Oct 12 '22 21:10

ARich