Excel VBA For loop breaks on execution of commands

[mlafrance1]

Hi,
I have a Excel 2010 spreadsheet that is frustrating me. The command that I enter inside a For loop executes but instead of going to the next For item (of which there are many) it exits the loop and starts the sub at the beginning again.
Here is the offending loop -

    Set rng = Range("J17:AA17")
    
    'Evaluate the holes for skins
    For Each myCell In rng
    If myCell.Value = "" Then
        MsgBox myCell.Address & " is not a skin cell."
        'populate the Carry Forward
        Range(myCell.Address).Offset(-16, 0).Value _                          <--
            = Range(myCell.Address).Offset(-16, 0).Value + Range("G18").Value <-- the offending code
    ElseIf myCell.Value = "Skin" Then
        MsgBox myCell.Address & " is a skin cell."
    End If
    Next myCell
    MsgBox "Finished the loop."

Any advice is very much appreciated.
Mike

 

[My Aswer Is This]

The script works for me and does not just keep stating the loop again.
If the loop never stopped it would run for ever.

You have told it to pop up a message Box every time it enters a new range.
Which means you will see a popup message 18 times.

It's always easier when a script does not do what you want it to do to tell us what you want the script to do and not just say the script is not doing what I wanted.

 

[RoryA]

I suspect this is a worksheet_change event? If so you need to disable events when changing cells in that code. Add

application.enableevents = false

before the for each line, then

application.enableevents = true

after the “Next” line.

 

[mlafrance1]

Thank you for your assistance, My Aswer Is This. iAny good advice is always welcomed.
Mike

The script works for me and does not just keep stating the loop again.
If the loop never stopped it would run for ever.

You have told it to pop up a message Box every time it enters a new range.
Which means you will see a popup message 18 times.

It's always easier when a script does not do what you want it to do to tell us what you want the script to do and not just say the script is not doing what I wanted.

 

[mlafrance1]

Thanks, Rory.
You were right, it was a worksheet change event. Your response resolved my immediate problem. Although I have other problems in my code your response was greatly appreciated.
Mike