VBE code to store values won't take formula input.

[TheresaMaybeImaFruitcake]

Hi all,
My title covers the reasonably well. I'm working on an end-to-end index, my first real attempt to do this myself - I've frankensteined in an excellent little bit of code which stores the values that my index outputs at each time interval. Trouble is, it only likes to run the process when I manually input the figure. So the code is supposed to store the value from the cell it references (in my case, C6) starting a few cells below, again and again, so I can use them later, in a list. All this works a treat if I type in my figure but it all goes horribly wrong if I: reference the C6 input it to another cell, give C6 any sort of formula, etc.
I've tried every form of excel recalculation (from hotkeys to automatic recalculation) to force it to accept C6's formula as a legitimate value for the code to store. The first time I reference another cell it does work, then it refuses to update my list with the new reference value(s) in C6.. I've narrowed it down to an issue with my code. Can anyone help? I've got a pretty hopeless grasp of VBE, never really having needed to use it before.
Code is below:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ; background-color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=011993]#011993[/URL] ; background-color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] }p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] ; min-height: 13.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=008f00]#008f00[/URL] ; background-color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] }span.s1 {color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=011993]#011993[/URL] }span.s2 {color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] }</style>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    i = 10


    'If there is a change to C6
    If Target.Address = ActiveSheet.Range("C6").Address Then
        Do Until ActiveSheet.Cells(i, 3).Value = vbNullString
            i = i + 1
        Loop


        ActiveSheet.Cells(i, 3).Resize(, 1).Value = ActiveSheet.Range("C6:C7").Value
    End If
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=011993]#011993[/URL] ][FONT=Menlo]End[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=011993]#011993[/URL] ][FONT=Menlo]Sub[/FONT][/COLOR]

Any help would be very much appreciated.
Thanks very much!

 

[RoryA]

Welcome to the forum.

The Worksheet_Change event is not triggered by formula recalculation; it's only triggered by a change to the formula content of the cell (if it's a literal value, then the formula is the value). That's why entering the formula initially triggers it, but subsequent recalculations don't.

The best workaround will somewhat depend on your setup. You could either have the Change event monitor the cell(s) that your formula in C6 uses, or use the Worksheet_Calculate event and store the old value in a variable that you can check each time the sheet calculates (because the Calculate event doesn't tell you what formulas recalculated or if their values actually changed).

 

[TheresaMaybeImaFruitcake]

Hi Rory,
Appreciate your help. I'm reasonably sure I follow so far. I've taken a look at my code with that in mind but can't really see a way to implement the necessary change. As I say, VBE is new territory for me - but so far:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] ; background-color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=011993]#011993[/URL] ; background-color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] }p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] ; min-height: 13.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=008f00]#008f00[/URL] ; background-color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] }span.s1 {color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=011993]#011993[/URL] }span.s2 {color: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] }</style>Private Sub Worksheet_Calculate(ByVal Target As Range)
    Dim i As Integer
    i = 10


    'If there is a change to C6
    If Target.Address = ActiveSheet.Range("C6").Address Then
        Do Until ActiveSheet.Cells(i, 3).Value = vbNullString
            i = i + 1
        Loop


        ActiveSheet.Cells(i, 3).Resize(, 1).Value = ActiveSheet.Range("C6:C7").Value
    End If
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=011993]#011993[/URL] ][FONT=Menlo]End[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=011993]#011993[/URL] ][FONT=Menlo]Sub[/FONT][/COLOR]

So far from what I understand the event "Worksheet_Calculate" should have fixed my issue.. No? Its almost certainly not that simple is it. When you say: "The Calculate event doesn't tell you what formulas recalculated or if their values actually changed" that does make some sense but I can't really fathom how to approach this.

Thanks again.

 

[RoryA]

The Calculate event doesn't provide a Target argument for the reasons I mentioned.

You could try something like this:

Option Explicit
Private Sub Worksheet_Calculate()

    Dim keyRange As Range
    Dim outRow As Long
    
    Set keyRange = Range("C6")

    outRow = Cells(Rows.Count, "C").End(xlUp).Row
    ' don't want a row earlier than 10
    If outRow < 9 Then outRow = 9
    'If there is a change to C6
    If keyRange.Value <> Cells(outRow, "C").Value Then
        On Error GoTo clean_up
        Application.EnableEvents = False
        Cells(outRow + 1, "C").Value = keyRange.Value
        Application.EnableEvents = True
    End If

clean_up:
    Application.EnableEvents = True

End Sub

This will basically test if the value in C6 is different from the last value stored from C10 downwards. If it is, it will add it as a new value at the bottom.

 

[TheresaMaybeImaFruitcake]

It's only bloody worked. Thanks very much indeed! I'll be sure to return the favor on this forum in the future.
Cheers

 

[RoryA]

Glad to help.