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 !
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...
=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...
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
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>...
I am very grateful !!
Tom Ogilvy <Thomas....@hqda.army.mil> wrote in message
news:exU#EPAk$GA.197@cppssbbsa05...
HTH,
Gary Brown
Rob C. <rw...@mindspring.com> wrote in article
<8as7ro$7s2$1...@nntp9.atl.mindspring.net>...
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...
Gary Brown <GBrow...@Yahoo.com> wrote ..
> How about: Chr(64 + lngCOLNUMBER)
> Rob C. <rw...@mindspring.com> wrote
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...
> > >> >