'Loop Until' Problem

[Iwasiw35]

Having issues with the below 'Do....Loop Until' not stopping when the data stops.

In the sample attached there are only three rows of data but the code keeps looping for a long time. I've tried a few different combinations of 'Do While' and 'Loop Until' and just can't figure it out. Any suggestions will be greatly appreciated.

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

You are testing for a range which always exists, even though the cells defined by the range are blanks.

Instead end your loop with

Please Login or Register  to view this content.

Regards

[tigeravatar]

Iwasiw35,

Alternate coding method:

Please Login or Register  to view this content.

[TMS]

What, exactly is this routine doing?

You set myrng as a number of cells on row 1. Then you offset down the page.

I don't think myrng will ever be "nothing", it's always going to be a group of cells.

You'd be better establishing the last row of data and then counting down to that ... if that's what you're trying to do.

Regards

[tigeravatar]

Iwasiw35,

The end goal can also be accomplished with a formula. In Cell A1:
=INDEX($C1:$Z1,,LOOKUP(2,1/(ISNUMBER(SEARCH(CHOOSE(COLUMN(),"p","f")&"a01",$C1:$Z1))),COLUMN($C1:$Z1)-2)-6)

Then copy to cell B1 and drag both down

[Iwasiw35]

Thanks for the suggestions everyone. I tried Richard's method (he got in first) and it worked a treat. Good lesson for the future.