June 7, 2020

Run Macro when Result of Formula Changes

Posted on by Dinesh Kumar Takyar

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.

3 thoughts 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.

    1. Hi, thanks for a good video!
      I tried it out but have got an error (se below)

      lastrow = Application.CountA(Sheet2.Range(“A:A”))

      At my 365 latest uppdate this will not work.
      My language is not set to English but should not have anything to say in VBA

Comments are closed.