For ..Next loops quitting Sub upon completion

[MartinF]

Hi,

I have a problem with a Sub quitting after the completeion of a for..next loop. The code i am using is as follows.

With reportsheet
.Cells(1, 1).Value = title
.Cells(27, 2).Value = tasks
.Cells(30, 2).Value = totalTimeOnSite


s1KeymanSub.MoveLast: s1KeymanSub.MoveFirst
For i = 34 To (34 + s1KeymanSub.RecordCount)
.Cells(i, 1).Value = s1KeymanSub.Fields(0): .Cells(i, 2).Value = s1KeymanSub.Fields(1)
Call cellBorder(.Cells(i, 1), xlEdgeTop, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 1), xlEdgeLeft, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 1), xlEdgeRight, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 1), xlEdgeBottom, xlContinuous, xlThin, 0)

Call cellBorder(.Cells(i, 2), xlEdgeTop, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 2), xlEdgeLeft, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 2), xlEdgeRight, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 2), xlEdgeBottom, xlContinuous, xlThin, 0)
s1KeymanSub.MoveNext
Next i


s1AreaSub.MoveLast: s1AreaSub.MoveFirst
For i = 34 To (34 + s1AreaSub.RecordCount)
.Cells(i, 6).Value = s1AreaSub.Fields(0): .Cells(i, 7).Value = s1AreaSub.Fields(1)
Call cellBorder(.Cells(i, 6), xlEdgeTop, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 6), xlEdgeLeft, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 6), xlEdgeRight, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 6), xlEdgeBottom, xlContinuous, xlThin, 0)

Call cellBorder(.Cells(i, 7), xlEdgeTop, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 7), xlEdgeLeft, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 7), xlEdgeRight, xlContinuous, xlThin, 0)
Call cellBorder(.Cells(i, 7), xlEdgeBottom, xlContinuous, xlThin, 0)
s1AreaSub.MoveNext
Next i

End With


Basically what happens is after the 1st loop has completed its iterations the Sub quits and control is passed back to the calling function. I am absolutely puzzled as to why the code does not carry on to process the second loop.

Any ideas?

Thanks

MartinF

 

[MartinF]

Just found the problem,

The recordset within the loop is producing a 'record not found error', but throwing the error back to the calling procedure rather than the procedure above that produces the error.

MartinF

 

[MichaelRed]

You MAY want to consider combining the loops, as -with the exception of the cell addrress [1 vs 6] they appear to be similar enough to place in the same loop.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MartinF]

Hi MichaelRed,

Yeah, thats a good point, i may try that

MartinF