Getting column names in Excel 2000
How can I get the letter column names in Excel in code? I want to apply a formula (SUM) to a range of cells in a row, but I can't use the Cells property because that takes integers, so instead of B8 i get 28.
I don't know all the columns in advance (varies by client), so I can't hardcode it. I can think of awkward ways -adding 65 & taking the ASCII value, creating an array of letters, etc, but it seems like there ought to be a better way - column.name or something. Thanks.
Re: Getting column names in Excel 2000
no simple way, but here's a function you can use:
VB Code:
Function xl_Col(ByRef Col_No) As String
'returns Excel column name from value
'(eg: col_no 27 returns "AA")
'Only allow valid columns
If Col_No < 1 Or Col_No > 256 Then Exit Function
If Col_No < 27 Then 'Single letter
xl_Col = Chr(Col_No + 64)
Else 'Two letters
xl_Col = Chr(Int((Col_No - 1) / 26) + 64) & _
Chr(((Col_No - 1) Mod 26) + 1 + 64)
End If
End Function
Re: Getting column names in Excel 2000
Thanks. You'd think the letter name would be accessible through a property, but oh well.