converting column number to column heading

ces005

Board Regular
Joined
Mar 13, 2006
Messages
189
Hi,

If I want to convert a column number into a column heading, I think I would do the following if I wanted to convert column 34 into the excel column heading reference ("AH"):

Substitute(Address(1, 34, 4), "1", "")

My question is how do I do the equivalent of this within a visual basic script?

I have a loop which calculates the column number. If the loop determines the column number is 34, I need to put this column heading into a variable.

-Eileen
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Erdinç E. Karaçam

Board Regular
Joined
Sep 23, 2006
Messages
202
Try this code For 1th row:

Code:
Sub ColumnHeaders()
    
    Dim i As Integer
    
        For i = 1 To 256
            Cells(1, i) = WorksheetFunction.Substitute(Cells(1, i).Address(0, 0), "1", "")
        Next i

End Sub
 

ces005

Board Regular
Joined
Mar 13, 2006
Messages
189
I added your logic to the following. What type should I declare DateCol? Currently it is a string. I am getting a 1004 run-time error when it tries to execute the Substitute function.

Code:
     ' LastCol was calculated to be 34 earlier
      DateCol = WorksheetFunction.Substitute(Cells(1, i).Address(0, 0), "1", "")
      LR = Cells(Rows.Count, "A").End(xlUp).Row
      
      ' apply the formula starting from the second row since the first row
      ' contains headings
      With Range(Cells(2, LastCol), Cells(LR, LastCol))
          .Formula = "=TEXT((" & DateCol & "2),""mmm-yy"")"
      End With
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try
Code:
Sub test()
Dim i As Integer, txt As String

For i = 1 To Columns.Count
   txt = Cells(1,i).Address(1,0)
   Cells(1,i).Value = Left(txt,InStr(txt,"$")-1)
Next

End Sub
 

ces005

Board Regular
Joined
Mar 13, 2006
Messages
189

ADVERTISEMENT

Cells(34,1).Address(1,0) yields B34 instead of AH

Column 34 is labelled "AH". I would simply like to know what logic to use to convert "34" into "AH". Thanks.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Cells(34,1).Address(1,0) yields B34 instead of AH

Column 34 is labelled "AH". I would simply like to know what logic to use to convert "34" into "AH". Thanks.

Ooops
Should be

Cells(1,34).Address(1,0)
 

Erdinç E. Karaçam

Board Regular
Joined
Sep 23, 2006
Messages
202

ADVERTISEMENT

Hi,

For a sample to Writing TEXT formula by VBA:
A4 Cell is a date as: 20/03/2006

Code:
Sub WRITING_TEXT_FORMULA_TO_A_WORKSHEET_BY_VBA()

    Dim SingleSeperator As String
    Dim Parenthesis As String
    Dim Format As String
    Dim DateCol As String
    Dim FormulaSeperator As String
    
    Seperator = """"
    SingleSeperator = Mid(Seperator, 1, 1)
    Parenthesis = ")"
    Format = "mmm-yy"
    DateCol = "A"
    FormulaSeperator = ","
    
    [B4] = "=TEXT(" & DateCol & 4 & FormulaSeperator & SingleSeperator & Format & SingleSeperator & Parenthesis
    
End Sub
 

ces005

Board Regular
Joined
Mar 13, 2006
Messages
189
Thanks for all of your help. I combined the code from the postings together.

I ended up with the following:
Code:
   ' find column titled "Ended-on"
   Do Until ((Cells(1, EndedOnCol).Value = "Ended-on") Or (EndedOnCol >= LastCol))
      EndedOnCol = EndedOnCol + 1
   Loop

   LR = Cells(Rows.Count, "A").End(xlUp).Row
   Cells(1, LastCol).Value = "ConvertedDate"
      
   'Assigns Letter value to EndedOnCol value
   TempCol = (Cells(2, EndedOnCol).Address(1, 0))
   DateCol = Left(TempCol, InStr(TempCol, "$") - 1)

   With Range(Cells(2, LastCol), Cells(LR, LastCol))
       .Formula = "=TEXT(" & DateCol & "2,""mmm-yy"")"
   End With
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Cells(34,1).Address(1,0) yields B34 instead of AH

Column 34 is labelled "AH". I would simply like to know what logic to use to convert "34" into "AH". Thanks.
Hi Eileen:

How about this convoluted formulation of mine ...
Code:
left(cells(1,34).address(0,0),len(cells(1,34).address(0,0))-1)
 

Forum statistics

Threads
1,137,202
Messages
5,680,145
Members
419,886
Latest member
Pulpboi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top