EXCEL - need to get column letter for an active cell - ie: "DZ"

[lakers8175]

I have a looping copy/paste macro that runs through a certain # of rows and a certain # of columns.

I'd like to figure out how to know what column I am on and stop the macro at a specified column, ie: column "DZ"

Since this macro is used on spreadsheets of varying lengths, I was planning on setting a variable with the column letter, and then checking in the loop until I reach my variable.

Does someone have a snippet of code that may be useable?

Thanks.

 

[SkipVought]

Hi,

You do not need column letter designations to reference columns. Much easier to do so with numerics.

If you have a row of contiguous data (like headings, for instance in row 1), then

With Range(Cells(1, 1), Cells(1, 1).End(xlToRight))
  iLastCol = .Columns.Count
End With

or

With [A1].CurrentRegion
  iLastCol = .Column.Count
  lLastRow = .Rows.Count
End With

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[PHV]

Have a look at the Column property of the Range object:
? Range("DZ1").Column
130

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GlennUK]

I'd say take into account what Skip and PHV have said, but here's a way to do it if you still think you need to get the column letter of a particular column ( within an example ):

Sub stance_abuse()
    myCod = InputBox("Which column do you want to process to?")
    sTestCol = ""
    iColPointer = 0
    Do Until sTestCol = myCod
        iColPointer = iColPointer + 1
        Cells(1, iColPointer) = "Hello"
        sTestCol = Cells(1, iColPointer).Address(True, False)   ' get address of current cell
        sTestCol = Left(sTestCol, InStr(1, sTestCol, "$") - 1) ' get letters of column
    Loop
End Sub

Cheers, Glenn.

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