Column Number/Letter

  • Thread starter Thread starter david fixemer
  • Start date Start date
D

david fixemer

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
 
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
 
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)
 
? 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.
 
Back
Top