loop problem

  • Thread starter Thread starter joao
  • Start date Start date
J

joao

Hi

i am using this loop to retrieve data from a recordset. The problem is
that when the while loop ends, it goes to the next (line 10) which is
fine, but then it goes to line 3 and not to line 2 where it was
supposed to go to the next c (the next field in the recordset). I have
already tried to put the next just after the for, and it worked fine
(but in this case its only possible to get the fields names or values,
but not all records). i have also tried to replace the while fo another
for... next but it didn´t worked also.

Any clues?

1 With rstRecordset
2 For Each c In .Fields
3 b = b + 1
4 While Not .EOF
5 ThisWorkbook.Sheets("entradas").Select
6 Cells(a, b).Value = c.Value
7 a = a + 1
8 rstRecordset.MoveNext
9 Wend
10 Next c
11 End With
 
I think you have a basic problem with your code. The FOR EACH loop should
be inside the While loop, not the other way around.

With rstRecordSet
Do Until .EOF() = True
A = A + 1
B = 1
For Each C In .Fields
Cells(A, B).Value = C.Value
B = B + 1
Next C
.MoveNext
Loop
End With

You might also want to see the CopyFromRecordSet method. E.g.,

Range("A1").CopyFromRecordSet(rstRecordSet)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
This must be very slow. Have a look as CopyFromRecordSet
in the help file: line 5 ought to be outside the With
block of code; I can't see the initial value of a.
 
but then it goes to line 3 and not to line 2

No it doesn't. That's just an illusion of the debugger.

Let me prove that with a routine very similar to yours but without the ADO
trappings:

Sub Test()
Dim Cell As Range
Dim a As Integer, b As Integer
With Sheet1
For Each Cell In .Range("A1:A10")
b = b + 1
a = 0
While Not a > 5
Cell.Offset(0, a).Value = b & " " & a
a = a + 1
Wend
Next Cell
End With
End Sub

While the debugger never (seemingly) gets back to the For Each, you can
clearing see from the output that "Cell" is being incremented.
 
Back
Top