'Loop Until' Problem

**Iwasiw35** · 11-08-2011, 07:24 PM

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** · 11-08-2011, 07:36 PM

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** · 11-08-2011, 07:40 PM

Iwasiw35,

Alternate coding method:

Please Login or Register  to view this content.

**TMS** · 11-08-2011, 07:41 PM

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** · 11-08-2011, 07:53 PM

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