UDF producing #NAME?

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" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> On Sat, 23 Apr 2005 05:46:45 -0400, "Jim May" <[email protected]> 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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > On Sat, 23 Apr 2005 05:46:45 -0400, "Jim May" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > On Sat, 23 Apr 2005 05:46:45 -0400, "Jim May" <[email protected]> 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! *
> >
> >
>
>