HELP WITH LOOPS!!!!

[chrisnichols87]

Can anyone see the problem with the following loop?

The nested loop when on it's own, correctly moves the associated cells
to the 1st record of the sheet "TESTING". However, when put inside the
loop which should do the above for every record, it takes a while and
then errors with:

"Run-time error '1004'
Application defined or object defined error"

The code is:


Dim ca As Long, ta As Long, tb As Long


ca = 3
ta = 1
tb = 1

Dim firstcomplete As Boolean
Dim secondcomplete As Boolean

firstcomplete = False


Do While Cells(ta, 1).Value <> ""

Sheets("CountData").Select

Do While firstcomplete <> True

Sheets("CountData").Select


If Cells(ca, 1).Value = Sheets("TESTING").Cells(ta, 1) Then

Sheets("CountData").Select

Range(Cells(ca, 2), Cells(ca, 99)).Select

Selection.Copy

Sheets("TESTING").Select

Cells(tb, 8).Activate

ActiveSheet.Paste

firstcomplete = True


Else

ca = ca + 1


End If

Loop

ta = ta + 1
tb = tb + 1
ca = 3

firstcomplete = False


Loop

Thanks for any help,
_________________
Chris

 

[PCLIVE]

I cleaned up your code a bit below. However, you may need to tell us at
what line the code produces the error. I'm guessing:
If Cells(ca, 1).Value = Sheets("TESTING").Cells(ta, 1).Value

The reason I got an error on this line is because there was never a match
for Cells(ca,1) and Cells(ta,1). The result was that it kept looping until
it got to the end of the sheet and then on the very next loop, ca = 65537.
The last row in Excel is 65536, hence the error. See if that is the same
situation for you. If this is the case, then depending on what you are
trying to accomplish, you may try adding an IF statement below ca = ca + 1
If ca = 65537 Then firstcomplete = True

HTH.
Paul


Sub test()
Dim ca As Long, ta As Long, tb As Long
Dim firstcomplete As Boolean
Dim secondcomplete As Boolean

ca = 3
ta = 1
tb = 1

firstcomplete = False

Do While Cells(ta, 1).Value <> ""
Sheets("CountData").Select
Do While firstcomplete <> True
If Cells(ca, 1).Value = Sheets("TESTING").Cells(ta, 1).Value _
Then
Range(Cells(ca, 2), Cells(ca, 99)).Select
Selection.Copy
Sheets("TESTING").Select
Cells(tb, 8).Activate
ActiveSheet.Paste
firstcomplete = True

Else
ca = ca + 1
End If
Loop

ta = ta + 1
tb = tb + 1
ca = 3

firstcomplete = False


Loop
End Sub

 

[chrisnichols87]

I cleaned up your code a bit below. However, you may need to tell us at
what line the code produces the error. I'm guessing:
If Cells(ca, 1).Value = Sheets("TESTING").Cells(ta, 1).Value

The reason I got an error on this line is because there was never a match
for Cells(ca,1) and Cells(ta,1). The result was that it kept looping until
it got to the end of the sheet and then on the very next loop, ca = 65537.
The last row in Excel is 65536, hence the error. See if that is the same
situation for you. If this is the case, then depending on what you are
trying to accomplish, you may try adding an IF statement below ca = ca + 1
If ca = 65537 Then firstcomplete = True

HTH.
Paul


Sub test()
Dim ca As Long, ta As Long, tb As Long
Dim firstcomplete As Boolean
Dim secondcomplete As Boolean

ca = 3
ta = 1
tb = 1

firstcomplete = False

Do While Cells(ta, 1).Value <> ""
Sheets("CountData").Select
Do While firstcomplete <> True
If Cells(ca, 1).Value = Sheets("TESTING").Cells(ta, 1).Value _
Then
Range(Cells(ca, 2), Cells(ca, 99)).Select
Selection.Copy
Sheets("TESTING").Select
Cells(tb, 8).Activate
ActiveSheet.Paste
firstcomplete = True

Else
ca = ca + 1
End If
Loop

ta = ta + 1
tb = tb + 1
ca = 3

firstcomplete = False


Loop
End Sub

Thanks for that,

The line that the code errors on is
If Cells(ca, 1).Value = Sheets("TESTING").Cells(ta, 1) Then

With the code you have sent me, it still errors on this line

Thanks,

Chris

 

[Don Guillett]

It appears that this code could be GREATLY simplified with NO selections if
you will tell us what you are trying to do. Something like. Notice the
placement of the ( . ) dots. and the SAME size range.

for i=1 to cells(rows.count,"a").end(xlup).row
with sheets("testing")
if cells(i,1)=.cells(i,1) then
.range(cells(i,8),cells(i,107)).value= _
range(cells(i,2),cells(i,99)).value
end if
end with
next i

 

[PCLIVE]

Chris,

You confirmed what I suspected. Read the rest of my message. When you get
the error and you goto to Debug, you can hover your mouse over ca and you'll
probably notice that it displays 65537. That will be the problem. At this
point the code is trying to reference a cell that doesn't exist. Adding the
"If" line that I previously suggested may correct that. But as Don Guillett
stated, if you state what it is you are trying to accomplish, you'll
probably find a simpler solution.

Regards,
Paul