For each next

[Guest]

I have a macro that cycles through each cell in a range, eg

for each CELL in RANGE

This seems to work OK until I include something in the code that changes the
value of CELL, and then the macro just ends at that point.

I have tried declaring CELL as a range but no success.

Any clues as to why this might happen?

Regards, Kaval

 

[Norman Jones]

Hi Kaval,

Sub TestIt2()
Dim rCell As Range
Dim rng As Range

Set rng = Range("A1:A10")

For Each rCell In rng
With rCell
If IsNumeric(.Value) Then
.Value = .Value * 2
End If
End With
Next rCell

End Sub

 

[Norman Jones]

Hi Kaval,

Post your problematic code, if necessary.

 

[Guest]

Thanks, I since discovered I had a worksheet change event that was running
when I changed the cells and this was ending the code execution.

Thanks.

 

[Dana DeLouis]

Thanks, I since discovered I had a worksheet change event that was running

Would this idea help?

Sub Demo()
Dim Cell As Range

'// Don't be interrupted by "Worksheet_Change"
Application.EnableEvents = False
For Each Cell In [A1:C4]
Cell = Rnd
Next
Application.EnableEvents = True
End Sub