Forum Discussion

Bao_bab's avatar
Bao_bab
Copper Contributor
Feb 17, 2023

Copy a formula result to a cell in another sheet + add succesive formula results in consecutive rows

Hi,

 

I am running a partially randomize set of data and trying to find the best solutions depending on certain parameter changes. I need to "record" certain solutions and then compare different results for different parameters each time the randomized variables are recalculated.

 

I would like to do the to following:

 

  1. On Sheet1, cell S255, is the result of a formula =SUM(M252:S252)

  2. I need to automatically add that result (Sheet1 S255), to Sheet5, column A, starting at A1.

  3. Then, each time the formula is recalculated and the result changes, I need the new result to be added to the consecutive row to the previous result (so the second result would go to A2, third one to A3, and so on).

Looking for similar cases I have come to be able to do #1 and #2 using this event:

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim ws As Worksheet: Set ws = Sheets("Sheet5") 'declare and set the worksheet the data is to be copied into, amend the sheet name as required
If Target.Address = "$S$255" Then 'if anything changes in C6 or C9 in this sheet
   ws.Range("A1").Value = Target.Parent.Range("S255") 'copy the value from cell C10 in this sheet to Sheet2 in cell E5
End If
End Sub

Doing #3 is proving more challenging. What event would be suitable to do so?

 

Thanks in advance for your time and understanding!

excel
Formulas and Functions
Macros and VBA

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 17, 2023

    Bao_bab 

    Try this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet5")
    If Not Intersect(Range("M252:S252"), Target) Is Nothing Then
        ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1).Value = Range("S255").Value
    End If
End Sub
    • Bao_bab's avatar
      Bao_bab
      Copper Contributor
      Feb 20, 2023

      ThankHansVogelaar 

      For some reason, it is not working though. I keep getting the "Compile error: Method or data member not found" message.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 20, 2023

        Bao_bab 

        The code works as intended when I test it.

        Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

Resources