Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How to store the column Letter if column number is known ?

4 views
Skip to first unread message

Rob C.

unread,
Mar 16, 2000, 3:00:00 AM3/16/00
to
I want to store the column letter of a given number to a variable...

Dim lngCOLNUMBER as Long ' where lng is equal to the number of columns of
data (which can vary)
Dim strCOLLETTER as String

strCOLLETTER= ???????

What is the formula to return the Column Letter ?

Note: I am not asking for the ActiveCell's column letter.


Thanks !


Tom Ogilvy

unread,
Mar 16, 2000, 3:00:00 AM3/16/00
to
Here is one way:

strColletter =
Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))

from the immediate window:

colnumber = 10
? Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
J
ColNumber = 28
? Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
AB
ColNumber = 1
? Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
A

Regards,
Tom Ogilvy
MVP Excel

Rob C. <rw...@mindspring.com> wrote in message
news:8as7ro$7s2$1...@nntp9.atl.mindspring.net...

Patrick Molloy

unread,
Mar 17, 2000, 3:00:00 AM3/17/00
to
Tom, that's a nice formula. I gave this some thought with regard to using
the buily in CELL function. Best I could do was

=MID(CELL("Address"),2,LEN(CELL("Address"))-LEN(ROW())-2)

horrible init.

this is better

=MID(CELL("Address"),2,FIND("$",CELL("address"),2)-2)

I'll use your method in future. Thanks!

--
Patrick Molloy
Microsoft Excel MVP
www.xl-expert.com pat...@xl-expert.com
_________________________________


Tom Ogilvy <twog...@email.msn.com> wrote in message
news:uuiLEU8j$GA.242@cppssbbsa04...

Rob C.

unread,
Mar 17, 2000, 3:00:00 AM3/17/00
to
Thanks for the response Tom,

When I tried it in my code which is being run from Access97, I get...

Run-time Error '1004': Method 'Columns' of Object '_Global' failed

Help is not available. Am I missing a reference or something ?

Thanks,
Rob

Tom Ogilvy

unread,
Mar 17, 2000, 3:00:00 AM3/17/00
to
I would imagine you would need to qualify it with where you want to look.
The columns is a child of the sheet in this case

With xlApp.Activesheet

strColletter = _
Left(.Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))

End with

Regards,
Tom Ogilvy
MVP Excel

Rob C. wrote in message <8at1dv$css$1...@slb7.atl.mindspring.net>...

Rob C.

unread,
Mar 17, 2000, 3:00:00 AM3/17/00
to
You are correct...

I am very grateful !!

Tom Ogilvy <Thomas....@hqda.army.mil> wrote in message
news:exU#EPAk$GA.197@cppssbbsa05...

Gary Brown

unread,
Mar 17, 2000, 3:00:00 AM3/17/00
to
How about: Chr(64 + lngCOLNUMBER)

HTH,
Gary Brown

Rob C. <rw...@mindspring.com> wrote in article
<8as7ro$7s2$1...@nntp9.atl.mindspring.net>...

Chip Pearson

unread,
Mar 17, 2000, 3:00:00 AM3/17/00
to
"Patrick Molloy" <patrick...@xxxhotmail.com> wrote in message

> =MID(CELL("Address"),2,FIND("$",CELL("address"),2)-2)

This can cause problems when the worksheet recalculates -- CELL("address")
returns the address of the active cell, not the cell containing the formula.
Enter =CELL("address") in some cell, and then enter =NOW() in another cell,
and watch what happens to the value in the cell with the CELL function.

Better to use ADDRESS(ROW(),COLUMN()) to get the cell's address, so the
formula would be

=MID(ADDRESS(ROW(),COLUMN()),2,FIND("$",ADDRESS(ROW(),COLUMN()),2)-2)

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting Services
www.cpearson.com ch...@cpearson.com


"Patrick Molloy" <patrick...@xxxhotmail.com> wrote in message
news:#yDg31#j$GA.275@cppssbbsa04...


> Tom, that's a nice formula. I gave this some thought with regard to using
> the buily in CELL function. Best I could do was
>
> =MID(CELL("Address"),2,LEN(CELL("Address"))-LEN(ROW())-2)
>
> horrible init.
>
> this is better
>
> =MID(CELL("Address"),2,FIND("$",CELL("address"),2)-2)
>
> I'll use your method in future. Thanks!
>
> --
> Patrick Molloy
> Microsoft Excel MVP
> www.xl-expert.com pat...@xl-expert.com
> _________________________________
>
>

> Tom Ogilvy <twog...@email.msn.com> wrote in message
> news:uuiLEU8j$GA.242@cppssbbsa04...
> > Here is one way:
> >
> > strColletter =
> > Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
> >
> > from the immediate window:
> >
> > colnumber = 10
> > ? Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
> > J
> > ColNumber = 28
> > ? Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
> > AB
> > ColNumber = 1
> > ? Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
> > A
> >
> > Regards,
> > Tom Ogilvy
> > MVP Excel
> >

> > Rob C. <rw...@mindspring.com> wrote in message
> > news:8as7ro$7s2$1...@nntp9.atl.mindspring.net...

David McRitchie

unread,
Mar 17, 2000, 3:00:00 AM3/17/00
to
Your formula would only be good for Columns A through Z,
and will not handle columns AA through IV.

Gary Brown <GBrow...@Yahoo.com> wrote ..


> How about: Chr(64 + lngCOLNUMBER)

> Rob C. <rw...@mindspring.com> wrote

Dana DeLouis

unread,
Mar 17, 2000, 3:00:00 AM3/17/00
to
Just to throw out something totally different for Excel 2000...Dana DeLouis.


Function ColumnLetter(n As Integer) As String
ColumnLetter = Split(Columns(n).Address, "$")(2)
End Function


Sub Test()
Debug.Print ColumnLetter(1)
Debug.Print ColumnLetter(26)
Debug.Print ColumnLetter(27)
Debug.Print ColumnLetter(256)
End Sub

Returns:
A
Z
AA
IV


"Rob C." <rw...@mindspring.com> wrote in message

news:8at6a2$82s$1...@slb2.atl.mindspring.net...


> You are correct...
>
> I am very grateful !!
>
>
>
> Tom Ogilvy <Thomas....@hqda.army.mil> wrote in message
> news:exU#EPAk$GA.197@cppssbbsa05...
> > I would imagine you would need to qualify it with where you want to
look.
> > The columns is a child of the sheet in this case
> >
> > With xlApp.Activesheet
> >
> > strColletter = _
> > Left(.Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
> >
> > End with
> >

> > Regards,
> > Tom Ogilvy
> > MVP Excel
> >

> > Rob C. wrote in message <8at1dv$css$1...@slb7.atl.mindspring.net>...
> > >Thanks for the response Tom,
> > >
> > >When I tried it in my code which is being run from Access97, I get...
> > >
> > >Run-time Error '1004': Method 'Columns' of Object '_Global' failed
> > >
> > >Help is not available. Am I missing a reference or something ?
> > >
> > >Thanks,
> > >Rob
> > >
> > >
> > >
> > >

> > >Tom Ogilvy <twog...@email.msn.com> wrote in message
> > >news:uuiLEU8j$GA.242@cppssbbsa04...
> > >> Here is one way:
> > >>
> > >> strColletter =
> > >> Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
> > >>
> > >> from the immediate window:
> > >>
> > >> colnumber = 10
> > >> ? Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
> > >> J
> > >> ColNumber = 28
> > >> ? Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
> > >> AB
> > >> ColNumber = 1
> > >> ? Left(Columns(colNumber).Address(false,false),1+(Colnumber>26)*(-1))
> > >> A
> > >>
> > >> Regards,
> > >> Tom Ogilvy
> > >> MVP Excel
> > >>
> > >> Rob C. <rw...@mindspring.com> wrote in message
> > >> news:8as7ro$7s2$1...@nntp9.atl.mindspring.net...

> > >> > I want to store the column letter of a given number to a
variable...
> > >> >

0 new messages