June 7, 2020

Run Macro when Result of Formula Changes

How to automatically run a macro when the result of a formula changes a cell value using VBA. Watch the video below:

Run Macro when Result of Formula Changes

The complete VBA code to execute the macro when the outcome of the procedure changes:

Private Sub Worksheet_Calculate()
Static MyOldVal
If Range(“G4”).Value <> MyOldVal Then
Call CopyData
MyOldVal = Range(“G4”).Value

End If
End Sub

Sub CopyData()
Dim lastrow As Long
lastrow = Application.WorksheetFunction.CountA(Sheet2.Range(“A:A”))
Sheet2.Range(“A” & (lastrow + 1)) = Sheet1.Range(“G4”)
End Sub

This solution can be quite useful in situations where you are getting data at regular intervals from a source like the stock-exchange.

One thought on “Run Macro when Result of Formula Changes

  1. Hi,
    Thank you for this code and video.
    Is it possible to use when you want to perform this calculation on multiple cells, each activating a separate macro?
    Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.