|
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" 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! * |
| All times are GMT +1. The time now is 01:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com