How do I get the column header from VBA?

[BluByU]

I hope this isn't a stupid question, but here goes.

If I know the index number of a column, how do I retrieve in VBA the alphabetic column header name?

Example:

Column 47 of the spreadsheet is "AU". The formulas I need to modify on a worksheet use the format: "=SUM('Master sheet v2.0'!AR14)"
So, I need to change the "AR" in the formula to "AU". Any suggestions on the simplest way to do this?

If I knew that Column index 47 was "AU", the VB code wouldn't be very difficult to write.

Thanks!

 

[Bong]

columns(index).address will return something like what you want. For instance, columns(2).address will return $B:$B.


_________________
Bob Rashkin

 

[SkipVought]

Hi,
[tt]
"=SUM('Master sheet v2.0'!AR14)"
[/tt]
can be written in VBA...

SomeCellObject.Formula = "=SUM('" & Activesheet.Name & "'!" & cells(14, "AU").address(false, false) & ")"

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[GlennUK]

I have to ask, why use SUM on a single cell reference? Anyway, that's beside the point ... use FormulaR1C1 to build formulas using row and column index numbers.

"=SUM('Master sheet v2.0'!$AU$14)"
can be written, in VBA, as:

SomeCellObject.Formula = "=SUM('Master sheet v2.0'!R14C47)"

Note the C47 denotes column 47.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.