Getting a macro to run continuously

[Ferru123]

Thank you.

The script works a dream when I manually type 1 into the cell I want to copy from.

The error message arises when the cell in question's value is 1 as the result of a formula being applied.

...and just for good measure, it boots me out of automatic calculation mode when it crashes !

Any suggestions?

Jeff

[Captain Sensible]

To be honest Jeff I have no idea what code you're using half the time,

just try something like this, it has error coding to hopefully catch the error and exit you at the point where it will restart you event/clacualtions. I'm away for a week later today and won't have much access to my PC but plenty of people around to help if you still can't get it working.

Code: Select all

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
On Error Goto Xit:



If [A1].Value = MyMarket Then

For i =5 to 10
If Range("AE" & i).Value=1 then Range("AF" & i).Value=Range("AE" & i).Value
next i

GoTo Xit
Else
MyMarket = [A1].Value

Range("AF5:AF50").Value = ""
End If


Xit:

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Ferru123]

Thanks Captain Sensible.

That appears to have done the trick.

I'm guessing you left out 'Dim i as integer' to see if I'd work it out for myself?

BTW, anyone learning how to program VBA macros to help with their automation could do a lot worse than to read this thread.

Thanks again, much appreciated.

Jeff