Is there a code statement that will convert the letter designation of
a column to a number, e.g., convert "A" to "1" and and so forth using
the letter pattern used to designate columns in a worksheed?

"AA" would be converted to "27".

I need to do arithmetic using the column numbers.

Thanks,

Fred Holmes

http://www.vba-programmer.com/Snippe...tr_To_Num.html


"Fred Holmes" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Is there a code statement that will convert the letter designation of
> a column to a number, e.g., convert "A" to "1" and and so forth using
> the letter pattern used to designate columns in a worksheed?
>
> "AA" would be converted to "27".
>
> I need to do arithmetic using the column numbers.
>
> Thanks,
>
> Fred Holmes

Give this a whirl...

Sub test()
MsgBox LetterToNumber("A")
MsgBox LetterToNumber("r")
MsgBox LetterToNumber("FA")
End Sub

Public Function LetterToNumber(ByVal Letter As String) As Long
Dim lngLength As Long
Dim lng As Long
Dim lngReturn As Long

lngLength = Len(Letter)
For lng = 1 To lngLength
lngReturn = lngReturn + (Asc(UCase(Mid(Letter, lng, 1))) - 64) _
* ((lngLength - lng) * 25 + 1)
Next lng
LetterToNumber = lngReturn
End Function
--
HTH...

Jim Thomlinson


"Fred Holmes" wrote:

> Is there a code statement that will convert the letter designation of
> a column to a number, e.g., convert "A" to "1" and and so forth using
> the letter pattern used to designate columns in a worksheed?
>
> "AA" would be converted to "27".
>
> I need to do arithmetic using the column numbers.
>
> Thanks,
>
> Fred Holmes
>

You can get the column number by:

msgbox cells(1,"AA").column
or
msgbox range("AA"&1).column

If you have a couple of range variables, you could use:

dim myCell_1 as range
dim myCell_2 as range

'set them to something

msgbox mycell_2.column - mycell_1.column
(to see the difference)

Fred Holmes wrote:
>
> Is there a code statement that will convert the letter designation of
> a column to a number, e.g., convert "A" to "1" and and so forth using
> the letter pattern used to designate columns in a worksheed?
>
> "AA" would be converted to "27".
>
> I need to do arithmetic using the column numbers.
>
> Thanks,
>
> Fred Holmes

--

Dave Peterson

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

=getcolnum("AA") - getcolnum("M") returns 14


Gord Dibben MS Excel MVP

On Thu, 15 Mar 2007 18:32:25 -0500, Dave Peterson <(E-Mail Removed)>
wrote:

>You can get the column number by:
>
>msgbox cells(1,"AA").column
>or
>msgbox range("AA"&1).column
>
>If you have a couple of range variables, you could use:
>
>dim myCell_1 as range
>dim myCell_2 as range
>
>'set them to something
>
>msgbox mycell_2.column - mycell_1.column
>(to see the difference)
>
>Fred Holmes wrote:
>>
>> Is there a code statement that will convert the letter designation of
>> a column to a number, e.g., convert "A" to "1" and and so forth using
>> the letter pattern used to designate columns in a worksheed?
>>
>> "AA" would be converted to "27".
>>
>> I need to do arithmetic using the column numbers.
>>
>> Thanks,
>>
>> Fred Holmes

This
msgbox range("AA"&1).column

should be
msgbox range("AA1").column
or
msgbox range("AA" & "1").column


Dave Peterson wrote:
>
> You can get the column number by:
>
> msgbox cells(1,"AA").column
> or
> msgbox range("AA"&1).column
>
> If you have a couple of range variables, you could use:
>
> dim myCell_1 as range
> dim myCell_2 as range
>
> 'set them to something
>
> msgbox mycell_2.column - mycell_1.column
> (to see the difference)
>
> Fred Holmes wrote:
> >
> > Is there a code statement that will convert the letter designation of
> > a column to a number, e.g., convert "A" to "1" and and so forth using
> > the letter pattern used to designate columns in a worksheed?
> >
> > "AA" would be converted to "27".
> >
> > I need to do arithmetic using the column numbers.
> >
> > Thanks,
> >
> > Fred Holmes
>
> --
>
> Dave Peterson

--

Dave Peterson

why not just:

columns("AA").column

--


Gary


"Fred Holmes" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Is there a code statement that will convert the letter designation of
> a column to a number, e.g., convert "A" to "1" and and so forth using
> the letter pattern used to designate columns in a worksheed?
>
> "AA" would be converted to "27".
>
> I need to do arithmetic using the column numbers.
>
> Thanks,
>
> Fred Holmes