none
VBA to update formula when a value changes in a refence range RRS feed

  • Question

  • Hi,

    I have an Excel formula that gives me the last Friday's date "=TODAY()-WEEKDAY(TODAY())-1" in cell A1

    I want to update cell A1 only when the values in reference range in another worksheet B2:D469 changes.

    Below is the code i am using but the issue is code only works when i manually make a change in the range. However values in the range gets updated when source pivot table refresh. I want code to get updated when i refresh the pivot table and the values in the range "B2:D469" changes.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    
    Set KeyCells = Worksheets.("Source").Range("B2:D469")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
    Is Nothing Then
    
    Worksheets.("Dashboard").Range ("A1").EnableCalculation = True
    
    End If
    End Sub
     

    Thursday, January 26, 2017 4:20 PM

All replies

  • Don't use a formula for this purpose. Instead, set the value of the cell in the code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim KeyCells As Range
        Set KeyCells = Range("B2:D469")
        If Not Intersect(KeyCells, Target) Is Nothing Then
            Worksheets("Dashboard").Range("A1").Value = Date - Weekday(Date) - 1
        End If
    End Sub

    Note that Range(Target.Address) is equivalent to Target, and that Worksheets.(...) is not valid. And EnableCalculation is a property of a worksheet, not of a range.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, January 26, 2017 4:35 PM