Hi

from Chip Pearson's site (I think) I got the following code:

Function ColumnLetter(R As Range) As String
ColumnLetter = Left(R.Address(False, False), _
1 - (R.Column 26) - (R.Column 702))
End Function

I was dead impressed as it does the trick in getting the column letters out
in Excel 2007 etc

My question is that the value true must have a value of minus one whereas I
thought it was nought as in Excel formulae. Can anyone give a simple
explanation, please?

Many thanks

Tim

hi Tim,

ActiveCell :
cln = Evaluate("SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN() ),""$"",""""),""1"","""")")

ActiveCell.Offset(,1) :
cln = Evaluate("SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN() +1),""$"",""""),""1"","""")")

ActiveCell.Offset(,-1) :
cln = Evaluate("SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN()-1),""$"",""""),""1"","""")")

--
isabelle

hi Isabelle

Not sure why you sent me these long formulae?

bw

Tim

"isabelle" wrote in message ...
hi Tim,

ActiveCell :
cln =
Evaluate("SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN() ),""$"",""""),""1"","""")")

ActiveCell.Offset(,1) :
cln =
Evaluate("SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN() +1),""$"",""""),""1"","""")")

ActiveCell.Offset(,-1) :
cln =
Evaluate("SUBSTITUTE(SUBSTITUTE(ADDRESS(1,COLUMN()-1),""$"",""""),""1"","""")")

--
isabelle

First off, here is a simpler function to use...

Function ColumnLetter(R As Range) As String
ColumnLetter = Split(R.Address, "$")(1)
End Function

As to your question... True in VBA is -1, False is 0 whereas in Excel
worksheet functions, TRUE is +1 and FALSE is 0.

Rick Rothstein (MVP - Excel)

Rick Rothstein has brought this to us :
First off, here is a simpler function to use...

Function ColumnLetter(R As Range) As String
ColumnLetter = Split(R.Address, "$")(1)
End Function

As to your question... True in VBA is -1, False is 0 whereas in Excel
worksheet functions, TRUE is +1 and FALSE is 0.

Rick Rothstein (MVP - Excel)

Note that Excel worksheet functions consider TRUE as any value 0.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Rick

Many thanks for neat alternative - very elegant, which is always good.
Thanks too for answer to initial question.

Best wishes

Tim

"Rick Rothstein" wrote in message
...
First off, here is a simpler function to use...

Function ColumnLetter(R As Range) As String
ColumnLetter = Split(R.Address, "$")(1)
End Function

As to your question... True in VBA is -1, False is 0 whereas in Excel
worksheet functions, TRUE is +1 and FALSE is 0.

Rick Rothstein (MVP - Excel)