Re: Example: how to convert a column letter to a number

[Ron de Bruin]

Hi Dan

Posted by Chip Pearson
******************

Function ColumnNumber(ColLetter) As Integer
ColumnNumber = Cells(1, ColLetter).Column
End Function

Temp = ColumnNumber("D") ' returns 4

To do the reverse, changing a number to a column letter, use

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber > 26))
End Function

Temp = ColumnLetter(26) ' returns "Z"

 

[Guest]

Thanks! Much easier than my way.

Hi Dan

Posted by Chip Pearson
******************

Function ColumnNumber(ColLetter) As Integer
ColumnNumber = Cells(1, ColLetter).Column
End Function

Temp = ColumnNumber("D") ' returns 4

To do the reverse, changing a number to a column letter, use

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber > 26))
End Function

Temp = ColumnLetter(26) ' returns "Z"