Do until loop that doesn't seem to end

[Sc0tt1e]

It worked once and found the forth valid cell in the range, ever since then it starts and then excel stops responding.

I'm guessing there is no end to the loop but not sure,can someone tell me whats up with this code please?

Please Login or Register  to view this content.

[stnkynts]

I fail to see the need for your Do/Loop. You are using a For/Next loop. Could you just add a line checking if IsEmpty(CellRef(x).Value) = True then Exit For?

[AB33]

Please Login or Register  to view this content.

[Sc0tt1e]

I fail to see the need for your Do/Loop. You are using a For/Next loop. Could you just add a line checking if IsEmpty(CellRef(x).Value) = True then Exit For?

Isn't that what the below code line is doing?

Please Login or Register  to view this content.

[AB33]

You will have an infinite loop if this line is true

Please Login or Register  to view this content.

For that not to happen CellRef(x).Value at some point in the loop need to be empty

[Norie]

Perhaps you need something other than IsEmpty to check the cell(s).

[Sc0tt1e]

Please Login or Register  to view this content.

If I understand correctly then yes the first Cellref0 will have a value and if I understood the code correct it will keep cycling through cellref2 2*4, cellref3 3*4 etc until it finds an empty cell, then it will stop, is this not correct?

[AB33]

Please Login or Register  to view this content.

will loop 11 times and for every loop CellRef(x) is set, but the last one would be loop 10 which is not zero and S2LIbe .Cells(RowNumba, 53 + (x * 4)) will not be unlikely to be zero.

Under what condition CellRef(x) is empty or zero?

[Sc0tt1e]

Please Login or Register  to view this content.

and so on and so forth when one of these values is empty I want the loop to stop hence the below code, this is when condition CellRef(x) will be empty

Please Login or Register  to view this content.

[Norie]

What's in the cells?

[AB33]

zero and empty are not the same. your data may return zero, but not empty.
You may try this line instead.

Please Login or Register  to view this content.

[Norie]

AB33

0 and "" aren't the same either.

[Sc0tt1e]

saved document locations (i.e. web address)

[Sc0tt1e]

Please Login or Register  to view this content.

That's the same as IsEmpty isn't it?

[Sc0tt1e]

CellRef(x) value will be blank or text, the cell value is not number based

[Norie]

"" is not the same as Empty, if you had a formula returning "" in the cell then the cell isn't empty.

[Sc0tt1e]

There are no formulas creating anything in these cells.

They are blank until another piece of code inputs a value into the first (horizontal) empty cell. Until then they are empty

[AB33]

Scottle,
We can not see what are in your data, but can only speculate and guess.
Empty cell means there is no data in cell (a formula which returns a null string as Norie explained IS NOT EMPTY).
So, my guess is I do not think the cell you are testing will be empty, but it may be blank.

[Sc0tt1e]

cells are either going to be empty of look like this C:\Users\Scott\Desktop\Barclays Questions.doc

[Norie]

Are you sure the Do loop is the problem?

[Sc0tt1e]

I think so. It is constructed as follows, I hope the logic is sound.

I know the SearchString, DocGrid, S2LI, Rownumba all work as I use them in other coding.

The only coding on this I don't fully comprehend is the loop.

the 1st for next sets the cells I want to check (rownumba starting at 53 and then moving every 4 cells)

Please Login or Register  to view this content.

This is basically a repetition of above set for the loop

Please Login or Register  to view this content.

This is the first line I want the grid to be produced on

Please Login or Register  to view this content.

The DoUntil users the x = 0 to 10 to cycle through the CellRef(x) - (Should this maybe "CellRef" & x ?)
Then with DocGrid the border formatting and cell merging is created and the values for CellRef(x) and it's offsets are placed into the relevant cells in Sheet1 (DocGrid)

Please Login or Register  to view this content.

Then this increases the i by one for each positive result in the CellRef search and then loops for the next

Please Login or Register  to view this content.

Then this increases the CellRef x for the next search criteria

Please Login or Register  to view this content.

[MrShorty]

One of the debugging skills I use most is the ability to step through the code one line at a time http://office.microsoft.com/en-us/su...819.aspx?CTT=1 If you have not yet tried setting a breakpoint in this loop and monitoring variables as you execute the loop, I would strongly suggest that you try it. (sometimes I wonder if debugging skills are more important than actually writing the code).

Stepping through the loop I see:
x=0
i=12
cellref(0) is not empty, so execute the loop
i=12+1=13
go back to start of loop
x=0 (still) so cellref(0) is still not empty, so execute the loop
and so on.

x never changes inside the Do until loop, so cellref(x) is never empty.

[Sc0tt1e]

Yeah, I haven't reached the debugging chapter of the book yet lol

So how do I change the x in the CellRef(x) within the Do until loop in line with the 0 To 10?

i=12+1=13
go back to start of loop

These 2 need to be the other way round (i think)
loop starts on i = 12 (line) 12
loop
next one (i = 12 + 1 = (line) 13)
loop
next one (i = 13 + 1 = (line) 14)

[MrShorty]

So how do I change the x in the CellRef(x) within the Do until loop in line with the 0 To 10?

If the compiler even allows you to modify the For..Next counter variable, I think it would be considered bad practice to modify x inside the loop. Beyond that, I'm not sure what to recommend. I would suggest you think through the underlying logic behind what you are trying to do here. Perhaps your real intention is to have the For x..Next x loop inside of the Do Until..Loop? Or perhaps the two loops are supposed to be combined so they are really one, single loop rather than two nested loops?

Another of those programming skills I remember learning was putting together a flow chart. If you don't have a flow chart overview of what you want these loops to accomplish, it might be worth the time to pull out a sheet of paper and think through what this loop is supposed to do.

[Sc0tt1e]

My flow is as follows

SearchString value, finds Rownumba

Search Rownumba and cell 56
is it empty?

if not paste the value and subsequent offset cells into row 12 with the cells merged, formatted etc (as per applymyformat)

then search the next cell value (rownumber & 60)

if not empty then do the same but move the pasted values to row 13

then search the next cell value (rownumber & 64)

if not empty then do the same but move the pasted values to row 14

keep doing this until the cell value is blank or up to the maximum of 11 loops is reached. This stops the loop

[MrShorty]

This description sounds to me like one single loop rather than two nested loops. Granted there are two counter variables (one to count through the "input" range, and one to count through the "output" range), but still one single loop. In a kind of pseudocode I see it as:

Please Login or Register  to view this content.

or something like that.

[Sc0tt1e]

Yes that's exactly what I'm looking for.

I have played with the code some more. It is now cycling through the x count and copy pasting 1-4 (as these currently have values and then stops the loop), but it isn't increasing the i count, it keeps pasting in line 1 instead of creating 4 lines.

I just can't work out where to place the i = i + 1 ?

Please Login or Register  to view this content.

[MrShorty]

I think you'd be able to catch this one if you stepped through the loop as I suggested earler. I would expect you to see within the 2nd or third time through that you are resetting i to 12 at the beginning of each loop.

[Sc0tt1e]

I was, that's how I worked out the placement of the x.
I have placed i = i + 1 in all the places I can think of but had no success.

2 things I have learnt from this, it cycles through all 10 x and only stops once it has cycled 10 times, the If Not only ends the call to ApplyMyFormat but still cycles through the 0-10.

The i never increases so even though it finds x1, x2, x3, and x4 and pastes the values into the boxes where it should it just overlays the info on line 12 instead of increasing new lines to paste the information into.

[MrShorty]

I was hoping you would see that it isn't so much about the placement of the i=i+1 statement as it is the i=12 statement. Follow the process:

x=0
i=12
copy and format
i=i+1=13
increment x (1) and return to i=12 statement
i=12 (again)
copy and format
i=i+1
and so on.

The problem is the placement of the i=12 statement -- this statement needs to be outside of the loop (before the For x= statement).

[protonLeah]

Replace i = 12 ---> i = 12 + x

[Sc0tt1e]

To MrShorty

Whilst you posted that I just worked it out lol

I was putting the i = 12 within the loop so everytime I was setting it as row 12

Please Login or Register  to view this content.