Run Macro when Value Changes

[RobMeads]

I have found several codes to do this and am now trying :

Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Not Intersect(Target, Range("D6, E6, D11, D12, D24, D25, D26")) Is Nothing Then
Run Correct
End If
Application.EnableEvents = True
End Sub

Part of the Sub Correct changes E6 to D6+180 if D6 is changed to a number >0 and <180; or to D6-180 if D6 is >180 and <361

The problem is that the sub Correct does not run when D6 is changed. It does run correctly if I go into the VBA Editor and run it from there.

Any help on making it run automatically gratefully received.

 

[vaskov17]

Post the code of 'Correct'

 

[RobMeads]

Post the code of 'Correct'

Sub Correct()
Dim Correct() As Excel.Application
Dim cr As Range
Dim Pi As Long


' Dim MySqrt as Double = Math.Sqrt()

' Pi = 3.14159265358979

For Each cr In Range("D16")
If UCase(cr) + Range("D17") <= 360 Then
Range("D18").Formula = "=(D16+d17)"
Range("D19").Formula = "=D18-(D17*2)"
ElseIf UCase(cr) + Range("D17") > 360 Then
Range("D18").Formula = "=(D16+D17)-360"
Range("D19").Formula = "=D18-(D17*2)"
End If
Next cr

For Each cr In Range("D19")
If UCase(cr) < 1 Then
Range("D19").Formula = "=(D18-(D17*2))+360"
ElseIf UCase(cr) + 0 > 360 Then
Range("D19").Formula = "=(D18-(D17*2))-360"
End If
Next cr

' tested to here

For Each cr In Range("D27")
If Range("D27") > 360 Then
Range("D27").Formula = "=D27-360"
ElseIf Range("D27") < 1 Then
Range("D27").Formula = "=D27+360"
End If

Range("D28").Formula = "=(D27+D24)"

If Range("D28") > 360 Then
Range("D28").Formula = "=D27+D24-360"
ElseIf Range("D28") < 1 Then
Range("D28").Formula = "=D27-D24+360"
End If

Range("D29").Formula = "=(Cos(D24 * Pi() / 180)) * D25"
Range("D30").Formula = "=(D26 - D29)"
Range("D31").Formula = "=Sqrt((D25 * D25) - (D29 * D29))"
Range("D32").Formula = "=D27 - ((Atan(D31 / D30) * 180 / Pi()))"


Next cr

For Each cr In Range("D32")
If UCase(cr) < 1 Then
' Range("D32") = "= D32 + 360"
Range("D32").Formula = "=(D27-((Atan(D31/D30)*180/Pi)))+360"
ElseIf UCase(cr) > 360 Then
Range("D32").Formula = "=(D27-((Atan(D31/D30)*180/Pi))-360)"
End If
Next cr


End Sub

 

[vaskov17]

Couple of things:

1. How are the cells "D6, E6, D11, D12, D24, D25, D26" changed? Are you typing in values directly in those cells or is cell D6 holding a formula like =A5+10 or is it some other way? If you are entering data directly that would cause the 'Worksheet_Change' event to trigger, if you are changing cell A5 which causes D6 to change the event would not trigger.

2. The way you have Correct set up as a 'Sub' would cause the call 'Run Correct' to fail as I believe the 'Run' command is expecting a function. You should be getting an error, but you have not mentioned it. Try running 'Correct' by simply changing the call 'Run Correct' to 'Correct' or 'Call Correct'.

 

[RobMeads]

Hi. I have saved the prog to a file with a different name and it now runs automatically ! Thanks.