Auto Run Macro 1

[d23]

All,

I need to run a macro anytime a cell value changes, but the cell is a formula, not just a static value. I have tried the following code and it works fine if I manually enter a value in the target cell, but if I use a formula in the target cell it does not work. Is there a way to run the macro when a formula result changes? In my file cell AD61 is the sum of several cells.

Example of “Does Not Work Code”

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(Target, Range("AD61")) Is Nothing Then Speed
Application.EnableEvents = True

End Sub

Sub Speed()

Dim S As Double 'Speed RPM
S = Range("AD70")
Range("AD71").FormulaR1C1 = S

End Sub

Thanks
David

 

[MintJulep]

From Excel's help:

This event doesn't occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.

 

[Denial]

Taking MintJulep's contribution a bit further ...

You have this cell, let's call it Watched, and you want to run some VBA whenever the value of Watched's formula changes. Pick some spare cell somewhere on your worksheet well away from the action (or perhaps even on its own worksheet), and let's call this cell Copied. Now in your Worksheet_Calculate event handler do the following:

If the value of Watched does not equal the value of Copied then
Run whatever code you have in mind
Set Copied equal to Watched
End If

If you don't like the idea of using a cell for Copied, you could probably use a VBA global variable instead.

 

[MintJulep]

Yes, I think Denial's approach is the only way to check if a specific cell's result has changed by the calculate event.

You might want to bring this question over to Tek-Tips VBA Forum.

Your Speed function could be simplified and cleaned up:

Sub Speed()
Range("AD71").value = Range("AD70").value
End Sub

 

[d23]

MintJuelep

It took a few minutes to figure out how to write it, but it works. One Star for you!

David

 

[d23]

All,

This is what I have and it is working:

Private Sub Worksheet_Calculate()

Application.EnableEvents = False
Call Speed
Application.EnableEvents = True

End Sub

 

[d23]

For what it's worth I have 81 rows of inputs on sheet 1. There are another 10 sheets performing dynamic calculations based on the 100+ inputs on sheet 1. The worst calculation sheet (named friction) starts on cell A1 and ends on cell EK310. Through the calculations I must predict rotating speed, but most of the dynamic calculations are based on speed. I need to copy - paste a static predicted speed so I don't have a circular calculation from my dynamic speed calculation.

I'm sure this sounds a little strange, but it works.

Thanks again.

 

[MintJulep]

Sounds like Goal Seek or Solver could be useful.

 

[davidbeach]

If it will always converge, go into Options|Formulas and Enable Iterative Calculation. Makes many things possible that would otherwise result in circular reference errors or require macros.