Column Letter - Am I using the best method?

[Oorang]

Right now when I want the letter of far right column of a flat file I using

HR = Mid(Columns(Range("IV1").End(xlToLeft).Offset(0, 1).Column).Address, 2, InStr(Columns(Range("IV1").End(xlToLeft).Offset(0, 1).Column).Address, ":") - 2)

Is this the best way?

 

[Andrew Poulsom]

Another way:

With Range("IV1").End(xlToLeft).Offset(0, 1)
        MsgBox Left(.Address(False, False), InStr(1, .Address(True, False), "$") - 1)
End With

 

[Oorang]

Hmmm.. well that was useful, if for no other reason then I never noticed that you could turn off absolute refrences on address. But it leads me to believe that all solutions are going to involve extracting the letter from the column address? (I was hoping for something like:
Range("IV1").End(xlToLeft).Offset(0, 1).?ColumnLetter?

 

[Andrew Poulsom]

Why do you need the column letter?

 

[Oorang]

For this:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Function</SPAN> SheetCompiler()
<SPAN style="color:#00007F">Dim</SPAN> MB1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>     <SPAN style="color:#007F00">'Message Box 1</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> MB2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>     <SPAN style="color:#007F00">'Message Box 2</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> SN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>      <SPAN style="color:#007F00">'Sheet Number</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> X <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>       <SPAN style="color:#007F00">'Utility variable</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> HR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>    <SPAN style="color:#007F00">'Header Right</SPAN>
<SPAN style="color:#007F00">'Written by Aaron Bush 12/05/05 Free for Public Use</SPAN>
MB1 = MsgBox("Add sheet labels?", 65572, "Sheet Compiler")
SN = ActiveSheet.Index
<SPAN style="color:#00007F">If</SPAN> ActiveWorkbook.MultiUserEditing <SPAN style="color:#00007F">Then</SPAN> ActiveWorkbook.ExclusiveAccess
ActiveWorkbook.Sheets.Add Before:=Worksheets(SN)
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
NameSheet:
ActiveSheet.Name = "Compiled Sheet " & Format(Date, "mmddyy")
<SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN>
    MB2 = MsgBox("Default name already in use, delete?", 65572, "Sheet Compiler")
    <SPAN style="color:#00007F">If</SPAN> MB2 = 6 <SPAN style="color:#00007F">Then</SPAN>
        Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
        Sheets("Compiled Sheet " & Format(Date, "mmddyy")).Delete
        Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
        Err.Clear
        <SPAN style="color:#00007F">GoTo</SPAN> NameSheet
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Err.Clear
    ActiveSheet.Name = InputBox("Default name already in use, please enter a name for compilation sheet:", "Sheet Compiler", "Compiled Sheet " & Format(Date, "mmddyy"))
    <SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN>
        Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
        ActiveSheet.Delete
        Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
Sheets(SN + 1).Rows(1).Copy
ActiveSheet.Paste
<SPAN style="color:#00007F">If</SPAN> MB1 = 6 <SPAN style="color:#00007F">Then</SPAN>
    HR = Mid(Columns(Range("IV1").End(xlToLeft).Offset(0, 1).Column).Address, 2, InStr(Columns(Range("IV1").End(xlToLeft).Offset(0, 1).Column).Address, ":") - 2)
    Range(HR & "1") = "Sheet Name"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> TheEnd
<SPAN style="color:#00007F">For</SPAN> X = SN + 1 <SPAN style="color:#00007F">To</SPAN> Sheets.Count
Sheets(X).Range("2:" & Sheets(X).Range("A65536").End(xlUp).Row).Copy
Sheets(SN).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
<SPAN style="color:#00007F">If</SPAN> MB1 = 6 <SPAN style="color:#00007F">Then</SPAN> Range(HR & Range(HR & "65536").End(xlUp).Offset(1, 0).Row, Range("a65536").End(xlUp).Offset(0, Columns(HR).Column - 1).Address) = Sheets(X).Name
<SPAN style="color:#00007F">Next</SPAN> X
TheEnd:
End <SPAN style="color:#00007F">Function</SPAN>
</FONT>

 

[Andrew Poulsom]

You can use the cells property with a column number, eg:

Dim c As Integer
If MB1 = 6 Then
    c = Range("IV1").End(xlToLeft).Offset(0, 1).Column
    Cells(1, c) = "Sheet Name"
    End If