Column Character
 

An very basic question.
How to get a cell's column character, but not column
number? I mean for cells(1,1), I want to get a string
as "A", but not an integer 1.
The reason for that is when I work on Chart, it looks
dosen't accept
..SeriesCollection(1).XValues = Sheets("Sum").Range(cells
(2,1), cells(13,1))
only accept
..SeriesCollection(1).XValues = Sheets("Sum").Range
("A2:A13")
I need to convert cells to a "A2" format.
Thanks.

Column Character
 

Sub test()
MsgBox ColLetters(Cells(1, 1))
MsgBox ColLetters(Cells(1, 27))
End Sub

Function ColLetters(There As Range) As String
ColLetters = Mid$(There.Address, 2)
ColLetters = Left$(ColLetters, InStr(ColLetters, "$") - 1)
End Function

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Bin" wrote in message ...
An very basic question.
How to get a cell's column character, but not column
number? I mean for cells(1,1), I want to get a string
as "A", but not an integer 1.
The reason for that is when I work on Chart, it looks
dosen't accept
.SeriesCollection(1).XValues = Sheets("Sum").Range(cells
(2,1), cells(13,1))
only accept
.SeriesCollection(1).XValues = Sheets("Sum").Range
("A2:A13")
I need to convert cells to a "A2" format.
Thanks.

Column Character
 

this worked fine for me -

Sub Macro2()
Charts.Add
ActiveChart.ChartType = xlLineMarkers
With Sheets("sum")
ActiveChart.SetSourceData _
Source:=.Range(.Cells(2, 1), .Cells(13, 1)), _
PlotBy:=xlColumns
End With
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sum"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

End Sub


--
Regards,
Tom Ogilvy

Bin wrote in message
...
I have put the Sheets("Sum").activate before ADD chart.
The problem is I have put Sheets("Sum") before Range, why
range("A2:A13") works but not Range(cells(2,1), cells
(13,1))
Also, I can not use variable Range and Sheets. Error
message shows "Object Variable or With Block variable not
set" even I didi not use block. Range only shows range.
Thanks.

-----Original Message-----
I think you problem is because Sum isn't the active sheet
and the
unqualified cells(2,1) and cells(13,1) refers to the
activesheet - which
raises an error. Try this instead:

With Sheets("Sum")
set rng = .Range(.Cells(2,1),.cells(13,1))
End With

..SeriesCollection(1).XValues = rng

or

set sh = Sheets("Sum")
..SeriesCollection(1).XValues = sh.Range(sh.Cells
(2,1),sh.Cells(13,1))

--
Regards,
Tom Ogilvy


"Bin" wrote in message
...
An very basic question.
How to get a cell's column character, but not column
number? I mean for cells(1,1), I want to get a string
as "A", but not an integer 1.
The reason for that is when I work on Chart, it looks
dosen't accept
.SeriesCollection(1).XValues = Sheets("Sum").Range(cells
(2,1), cells(13,1))
only accept
.SeriesCollection(1).XValues = Sheets("Sum").Range
("A2:A13")
I need to convert cells to a "A2" format.
Thanks.



.