Column index name

[John]

Dear ALL,

I got a question that maybe a minor one to everyone. but anyway, if I know
the column number, how do I know what "alphabeth letter" refer to it? E.g.
I know the value c = 5 as referring column 5, but is there a way to return
the column letter "E" instead of 5? Please help since will need to use it
for setting formula inside macro. Many many thanks.

Regards,
John

 

[Tom Ogilvy]

Range("Z21").Formula = "Sum(" & Cells(3,c).Resize(10,1).Address & ")"

Generally you don't need the column letter by itself.


However,

Left(columns(c).Address(0,0),1 + -1*(c > 26))


from the immediate window:
c = 5
? Left(columns(c).Address(0,0),1 + -1*(c > 26))
E
c = 27
? Left(columns(c).Address(0,0),1 + -1*(c > 26))
AA

 

[JE McGimpsey]

You generally don't need to do this in a macro. instead of

Sheets("Sheet1").Range(Chr(64 + n) & "2").Formula = "=SUM(A1:B1)"

for instance, use

Sheets("Sheet1").Cells(2, n).Formula = "=SUM(A1:B1)"

or, if your range is in the formula:

Range("B1").Formula = _
"=SUM(" & Cells(2, n).Resize(10, 1).Address(0, 0) & ")"

 

[Don Guillett]

You don't need this for inside a macro
columns(3) is column C
range(cells(1,1),cells(1,3))