Both these functions are yielding/displaying the #NAME?
in cell A1 which contains either =ColumnLetter(AB5) or ColumnLetter($AB$5)
or
ColLetter(AB5) or ColLetter($AB$5)..
Any ideas as to why?
Thanks in Advance

Function ColumnLetter(Rng As Range) As String
ColumnLetter = Left(Rng.Range("A1").Address(True, False), _
InStr(1, Rng.Range("A1").Address(True, False), "$", 1) - 1)
End Function


Function ColLetter(Rng As Range) As String
ColLetter = Left(Rng.Address(False, False), _
1 - (Rng.Column 26))
End Function

On Sat, 23 Apr 2005 05:46:45 -0400, "Jim May" wrote:

Both these functions are yielding/displaying the #NAME?
in cell A1 which contains either =ColumnLetter(AB5) or ColumnLetter($AB$5)
or
ColLetter(AB5) or ColLetter($AB$5)..
Any ideas as to why?
Thanks in Advance

Best guess; you have the function definitions in a different workbook
from the workbook that you have the formulas in.

If the functions below are in your Personal.xls workbook (say), then
this:
=PERSONAL.XLS!ColumnLetter(AC15)

should work, but this:
=ColumnLetter(AB5)

gives me the result you describe.

You might also consider saving the functions into an add-in. (.xla)

Function ColumnLetter(Rng As Range) As String
ColumnLetter = Left(Rng.Range("A1").Address(True, False), _
InStr(1, Rng.Range("A1").Address(True, False), "$", 1) - 1)
End Function


Function ColLetter(Rng As Range) As String
ColLetter = Left(Rng.Address(False, False), _
1 - (Rng.Column 26))
End Function


---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Hank - Thanks,,,
CRAP!! -- I had them both in my Sheet1 module
versus a Standard Module..
Thanks,
Jim


"Hank Scorpio" wrote in message
...
On Sat, 23 Apr 2005 05:46:45 -0400, "Jim May" wrote:

Both these functions are yielding/displaying the #NAME?
in cell A1 which contains either =ColumnLetter(AB5) or
ColumnLetter($AB$5)
or
ColLetter(AB5) or ColLetter($AB$5)..
Any ideas as to why?
Thanks in Advance

Best guess; you have the function definitions in a different workbook
from the workbook that you have the formulas in.

If the functions below are in your Personal.xls workbook (say), then
this:
=PERSONAL.XLS!ColumnLetter(AC15)

should work, but this:
=ColumnLetter(AB5)

gives me the result you describe.

You might also consider saving the functions into an add-in. (.xla)

Function ColumnLetter(Rng As Range) As String
ColumnLetter = Left(Rng.Range("A1").Address(True, False), _
InStr(1, Rng.Range("A1").Address(True, False), "$", 1) - 1)
End Function


Function ColLetter(Rng As Range) As String
ColLetter = Left(Rng.Address(False, False), _
1 - (Rng.Column 26))
End Function


---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Hi Jim,

Another version for you :-)

Function ColumnLetter(rng As Range) As String
ColumnLetter = Split(Columns(rng.Column).Address(, False), ":")(1)
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:v5pae.26134$gV.17101@lakeread02...
Hank - Thanks,,,
CRAP!! -- I had them both in my Sheet1 module
versus a Standard Module..
Thanks,
Jim


"Hank Scorpio" wrote in message
...
On Sat, 23 Apr 2005 05:46:45 -0400, "Jim May" wrote:

Both these functions are yielding/displaying the #NAME?
in cell A1 which contains either =ColumnLetter(AB5) or
ColumnLetter($AB$5)
or
ColLetter(AB5) or ColLetter($AB$5)..
Any ideas as to why?
Thanks in Advance

Best guess; you have the function definitions in a different workbook
from the workbook that you have the formulas in.

If the functions below are in your Personal.xls workbook (say), then
this:
=PERSONAL.XLS!ColumnLetter(AC15)

should work, but this:
=ColumnLetter(AB5)

gives me the result you describe.

You might also consider saving the functions into an add-in. (.xla)

Function ColumnLetter(Rng As Range) As String
ColumnLetter = Left(Rng.Range("A1").Address(True, False), _
InStr(1, Rng.Range("A1").Address(True, False), "$", 1) - 1)
End Function


Function ColLetter(Rng As Range) As String
ColLetter = Left(Rng.Address(False, False), _
1 - (Rng.Column 26))
End Function


---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Thanks Bob, I've added it to my file(s).
Jim

"Bob Phillips" wrote in message
...
Hi Jim,

Another version for you :-)

Function ColumnLetter(rng As Range) As String
ColumnLetter = Split(Columns(rng.Column).Address(, False), ":")(1)
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:v5pae.26134$gV.17101@lakeread02...
Hank - Thanks,,,
CRAP!! -- I had them both in my Sheet1 module
versus a Standard Module..
Thanks,
Jim


"Hank Scorpio" wrote in message
...
On Sat, 23 Apr 2005 05:46:45 -0400, "Jim May" wrote:

Both these functions are yielding/displaying the #NAME?
in cell A1 which contains either =ColumnLetter(AB5) or
ColumnLetter($AB$5)
or
ColLetter(AB5) or ColLetter($AB$5)..
Any ideas as to why?
Thanks in Advance

Best guess; you have the function definitions in a different workbook
from the workbook that you have the formulas in.

If the functions below are in your Personal.xls workbook (say), then
this:
=PERSONAL.XLS!ColumnLetter(AC15)

should work, but this:
=ColumnLetter(AB5)

gives me the result you describe.

You might also consider saving the functions into an add-in. (.xla)

Function ColumnLetter(Rng As Range) As String
ColumnLetter = Left(Rng.Range("A1").Address(True, False), _
InStr(1, Rng.Range("A1").Address(True, False), "$", 1) - 1)
End Function


Function ColLetter(Rng As Range) As String
ColLetter = Left(Rng.Address(False, False), _
1 - (Rng.Column 26))
End Function


---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *