Column Number/Letter
 

Ladies & Gentlemen,

Is there an easy way to get activecell.column to return a
letter(s) rather than a number?

The reason I'm asking is that I would like to do the
following:

activecell.value = "=sum(AA1,AB2,AC3)"

My problem is that I have the "AA", "AB", and "AC" as
numbers rather than letters.

What about converting "Cells(2,28)" into AB2?

Is there a better methond of summing a "broken range"?

Thanks in advance,

David Fixemer

Column Number/Letter
 

Hi david

You can use a function

Function ColumnLetter(ColNumber) As String
'Chip Pearson
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function

Two ways

Sub test()
MsgBox ColumnLetter(ActiveCell.Column)

' Or this one without the function
MsgBox Left(ActiveCell.Address(, False), IIf(ActiveCell.Column 26, 2, 1))

End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"david fixemer" wrote in message ...
Ladies & Gentlemen,

Is there an easy way to get activecell.column to return a
letter(s) rather than a number?

The reason I'm asking is that I would like to do the
following:

activecell.value = "=sum(AA1,AB2,AC3)"

My problem is that I have the "AA", "AB", and "AC" as
numbers rather than letters.

What about converting "Cells(2,28)" into AB2?

Is there a better methond of summing a "broken range"?

Thanks in advance,

David Fixemer

Column Number/Letter
 

David,

How about

activecell.formular1c1= "=sum(R1C27,R2C28,R3C29)"

then you can use numbers

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"david fixemer" wrote in message
...
Ladies & Gentlemen,

Is there an easy way to get activecell.column to return a
letter(s) rather than a number?

The reason I'm asking is that I would like to do the
following:

activecell.value = "=sum(AA1,AB2,AC3)"

My problem is that I have the "AA", "AB", and "AC" as
numbers rather than letters.

What about converting "Cells(2,28)" into AB2?

Is there a better methond of summing a "broken range"?

Thanks in advance,

David Fixemer

Column Number/Letter
 

? cells(2,28).Address(0,0)
? cells(2,28).Address(0,0)
AB2

? "=Sum(" & union(cells(3,5),cells(11,2),cells(20,8)).Address( 0,0) & ")"
=Sum(E3,B11,H20)

or
? "=Sum(" & union(cells(3,5),cells(11,2),cells(20,8)).Address & ")"
=Sum($E$3,$B$11,$H$20)


I don't think I have ever come across an instance where I had to parse out
the column letter.

--
Regards,
Tom Ogilvy


"david fixemer" wrote in message
...
Ladies & Gentlemen,

Is there an easy way to get activecell.column to return a
letter(s) rather than a number?

The reason I'm asking is that I would like to do the
following:

activecell.value = "=sum(AA1,AB2,AC3)"

My problem is that I have the "AA", "AB", and "AC" as
numbers rather than letters.

What about converting "Cells(2,28)" into AB2?

Is there a better methond of summing a "broken range"?

Thanks in advance,

David Fixemer