What's wrong with my loop ?

[Grek]

Hi,

I have a problem with the macro that I have done.
I would like it to stop when the value of the cell = "END" but it doens't work. The macro never stops running.

Could you please have a look at the problem and tell me what's wrong ? I assume that there is also a better solution that all the loops that I've done... ?

Thanks for your help,

Greg

Please Login or Register  to view this content.

[mudraker]

Greg

When I ran you the macro from your workbook it was not the Do Until ActiveCell.Value = "END" that was causing your problem

Your problem is caused by this part of your code

Do Until ActiveCell.Value = "x"
ActiveCell.Offset(1, 0).Activate
Loop

What is happening is that after your Do Until ActiveCell.Value = "END" loop has run many times the at the activecell for yourDo Until ActiveCell.Value = "x" is in row 67 which has no more x's below it so the Do Until ActiveCell.Value = "x" loop continues until it runs out of rows at row number65536 then crashes

[peejay]

the following code will loop down the rows used in column A, but the problem is what are you trying to do with the "x" check you have used because it is where the problem is coming from.
Can you explain more, what do you need to move to the other sheet?

Code:

Sub ModifiedChecks()
'
'
'

Dim VarName
Dim Lastrow

'find last row used on "FULL" Sheet in column a
Lastrow = Sheets("FULL").Cells(Rows.Count, "A").End(xlUp).Row


VarName = Sheets("AAA CHECKS").Range("D6").Value

Sheets("FULL").Select
Range("A3").Select

For n = 1 To Lastrow '<< Loop from first to last row used in column A
Cells(n, 1).Activate '<< activate each cell in column A and check value
If ActiveCell.Value <> "END" Then
'
'You can copy values from "FULL" sheet to "AAA Check" sheet here
'
End If
Next

End Sub

[peejay]

Sorry, now i have taken a closer look, i think i see what your trying to do.
Try this code it seems to give the desired results

code:

Sub UpdateChecks()
'
' UpdateChecks Macro
' Macro recorded 12/12/2006 by
'

'
Dim VarDescription
Dim VarFrequency
Dim VarPeriod
Dim VarName

Sheets("AAA CHECKS").Select
VarName = Range("D6").Value
Range("B19").Select


Sheets("FULL").Select
Range("A3").Select

Do Until ActiveCell.Value = VarName
ActiveCell.Offset(0, 1).Activate
Loop

Do Until ActiveCell.Value = "END"
ActiveCell.Offset(1, 0).Activate

If ActiveCell.Value = "x" Then
VarDescription = ActiveCell.Offset(0, -3).Value
VarFrequency = ActiveCell.Offset(0, -2).Value
VarPeriod = ActiveCell.Offset(0, -1).Value
Sheets("AAA CHECKS").Select
ActiveCell.Value = VarDescription
ActiveCell.Offset(0, 1).Value = VarFrequency
ActiveCell.Offset(0, 2).Value = VarPeriod
ActiveCell.Offset(1, 0).Activate
Sheets("FULL").Select
End If
Loop

End Sub

[Grek]

Thanks a lot for your help Peejay