Storing Simulation Results

[dahowarduk]

I have designed a simulation spreadsheet whereby the result of my simulation can be found each time in cell A1.
When I run the 1st simulation I want to save that answer in cell B1.
When I run the 2nd simulation I want to save that answer in cell B2....
And so on for 200 simulations.

Could I use a macro for any number X of simulations, and record all of the X results separately in column B

Any tips?
Thanks

 

[Logit]

Option Explicit

Sub SaveSimulationResults()
    Dim ws As Worksheet
    Dim simulationCount As Long ' Change data type to Long to handle larger values
    
    ' Set the worksheet where the simulation data is stored
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
    
    ' Find the next empty row in column B for storing simulation results
    simulationCount = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row ' Get the last used row in column B
    
    ' Save the result from cell A1 to the next available cell in column B
    ws.Cells(simulationCount + 1, 2).Value = ws.Cells(1, 1).Value
    Range("A1").Select
End Sub

 

[dahowarduk]

Thanks...but I'm afraid I don't understand your tip.
Is it some sort of program or a macro?

 

[Logit]

Actually I've created a better version that is more automated. Here is the code and there is a link below so you can download the example workbook.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim nextEmptyRow As Long

    ' Set the worksheet (this macro is already tied to the sheet module)
    Set ws = Me
   
    ' Check if the change occurred in A1
    If Not Intersect(Target, ws.Range("A1")) Is Nothing Then
        ' Ensure A1 contains data before proceeding
        If ws.Range("A1").Value <> "" Then
            ' Find the first empty row in Column B starting from B2
            nextEmptyRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1
            If nextEmptyRow < 2 Then nextEmptyRow = 2 ' Ensure writing starts at B2
           
            ' Transfer the value from A1 to the next empty cell in Column B
            ws.Cells(nextEmptyRow, "B").Value = ws.Range("A1").Value
           
            ' Clear the data in A1
            Application.EnableEvents = False ' Prevent triggering the event again
            ws.Range("A1").ClearContents
            Application.EnableEvents = True
           
            ' Return the cursor to A1
            ws.Range("A1").Select
        End If
    End If
End Sub

https://secure.eu.internxt.com/d/sh/file/4a2a0ca8-fbef-49ed-815f-a5c9e0c44b58/c16b41a2435af186d55c53e2a614358eecbad36010d16e97f50686f0aa62e3a5

 

[alansidman]

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Logit]

Thanks alansidman . If you are referring to my last posted macro code, it needs to be pasted in the worksheet module so
it can be completely automated.