Feb 17 2023 09:20 AM
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:
On Sheet1, cell S255, is the result of a formula =SUM(M252:S252)
I need to automatically add that result (Sheet1 S255), to Sheet5, column A, starting at A1.
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!
Feb 17 2023 10:45 AM
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
Feb 20 2023 03:12 AM
Thank@Hans Vogelaar
For some reason, it is not working though. I keep getting the "Compile error: Method or data member not found" message.
Feb 20 2023 03:54 AM
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.
Feb 24 2023 03:01 AM - edited Feb 24 2023 03:02 AM
Thanks @Hans Vogelaar ! Went for the second option.
Feb 24 2023 04:20 AM
You copied the code into a standard module.
It should be copied into the worksheet module of Sheet1 instead. To activate this module, right-click the sheet tab of Sheet1 and select 'View Code' from the context menu.
See your private messages.