Solved: Do loop problem is last row (65536) reached

[hunsnowboard]

Hi there Everyone! I am trying to do a loop macro. My problem is that if the loop reaches the last row (row 65536) then the code stops with an error. How can I code the macro (if reaches the last row) to continue searching in the next worksheet. (by the way, for looping through worksheets I use the code found on this site! :yes)

So far my macro looks like this:


Sub Proba()
Dim i As Long
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

i = 1
Do While Cells(i, "a").Value <> ""
If Worksheets(1).Range("e3").Value = Cells(i, "b").Value Then
Cells(i, "a").Resize(1, 0).Copy Destination:=Worksheets(1).Range("e6")
End If
i = i + 1
Loop
'E.g.
'On Error Resume Next 'Will continue if an error results
'ws.Range("A1") = ws.Name
Next ws
End Sub

Thank you in advance!

[nst1107]

Use a conditional statement inside the loop to check value of i and exit loop if i = 65536. Such as If i = 65536 Then Exit Do

[Bob Phillips]

Do While Cells(i, "a").Value <> "" And i <= Rows.Count

[hunsnowboard]

Thank you! I made another way to work! Thank you anyway!

[mdmackillop]

Don't use a loop to check every cell to see if it equals one value, use Find instead. It's much quicker. If you need to find more than one instance, use a FindNext loop. (see VBA help for an example)