Do Until Column BBnn or whatever

[MPastor]

I'm trying to determine the code to use in VBA for a DO UNTIL where I want to end my LOOP when it reaches a predefined column, say Column BB.

The macro will continue processing column by column until it reaches the desired "last column", BB in this example.

I'm drawing a blank on how to capture the columnar portion of the cell address. ActiveCell.Column returns the numerical column value, not it's letter value, right??

In advance...THANKS for the assistance...

 

[just_jon]

It's just

RangeRef.Column

which returns a Long which can then be compared to

FixedColRef.Column

 

[MPastor]

Just_Jon, thanks for helping me out.

I seem to be doing something wrong since I'm getting Run-Time Error '424' Object Required error messages.

To see how these properties work, I'm simply using them in MsgBox statements so I can see the values they'll return.

    MsgBox "ActiveCell.Address: " & ActiveCell.Address
    MsgBox "RangeRef.Column: " & RangeRef.Column
    MsgBox "FixedColRef.Column: " & FixedColRef.Column

The first MsgBox statement works fine and displays a message window with: ActiveCell.Address: $K$7

I'm running Excel/Office 2003 with Windows XP Professional. Any suggestions??

 

[MPastor]

I was able to get it to work using

    Do While Mid(ActiveCell.Address, 2, (InStr(2, ActiveCell.Address, "$") - 2)) <= "BB"

where the above code "strips" out the columnar designation for the activecell.address which is in the format $aa$NN.

Thanks again for your efforts to help me.

I always enjoy learning new techniques so I'm still curious about RANGEREF.COLUMN and FIXEDCOLREF.COLUMN.

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> LastCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> Range
LastCol = [J1].Column
MsgBox "The column number of the last column we want processed is " & LastCol
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A1:IV1")
    <SPAN style="color:#00007F">If</SPAN> c.Column > LastCol <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Hey, we're stopping now."
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        MsgBox "We be working here!"
        MsgBox "We're only at column " & c.Column & " of " & LastCol
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[MPastor]

Just_Jon, I guess I took you too literally and didn't realize the "range reference" you were making. Thanks for the clarification...

 

[just_jon]

Just_Jon, I guess I took you too literally and didn't realize the "range reference" you were making. Thanks for the clarification...

Sorry I was not clear-er.