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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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
Back
Top