VBA & Macro Problems

Append Recalculated Excel Formula Results to the Next Available Row

Bushra ParveenBushra Parveen Nov 18, 2026 868 views

Question details

Record the formula result in S255 on the next available row of Sheet5 when the relevant inputs change or recalculate.

Append Recalculated Excel Formula Results to the Next Available Row
Product
Microsoft Excel
Device & OS
not provided
Scenario
M252:S252 contain inputs or randomized formulas, and each relevant result from S255 should be appended to column A of Sheet5.
Observed behavior
Worksheet_Change can respond to direct edits, but formula recalculation alone does not trigger that event.
Before you start

Confirm that M252:S252 and S255 are on the worksheet whose event code you will edit, and that the destination worksheet is named Sheet5. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and enable macros if prompted.

Solution 1Recommended

Use Worksheet_Calculate for recalculated formula results

Use this method when the randomized values change through formula recalculation rather than direct edits.

Worksheet_Calculate runs whenever the source worksheet recalculates, not only when M252:S252 change. This example appends the first observed S255 value and then appends only when that value differs from the previous recorded value.

1
Open the worksheet module

In Excel, right-click the sheet tab containing S255 and select View Code. Paste the following code into that worksheet's module, replacing any existing Worksheet_Calculate procedure there: Private Sub Worksheet_Calculate() Static busy As Boolean Static hasLast As Boolean Static lastValue As Variant Dim currentValue As Variant Dim logSheet As Worksheet Dim nextRow As Long If busy Then Exit Sub currentValue = Me.Range("S255").Value2 If IsError(currentValue) Then Exit Sub If hasLast Then If currentValue = lastValue Then Exit Sub End If busy = True On Error GoTo Failed Set logSheet = ThisWorkbook.Worksheets("Sheet5") If Application.WorksheetFunction.CountA(logSheet.Columns("A")) = 0 Then nextRow = 1 Else nextRow = logSheet.Cells(logSheet.Rows.Count, "A").End(xlUp).Row + 1 End If logSheet.Cells(nextRow, "A").Value = currentValue lastValue = currentValue hasLast = True CleanUp: busy = False Exit Sub Failed: MsgBox "Could not append the result: " & Err.Description Resume CleanUp End Sub

2
Test a recalculation

Return to the worksheet and press F9 to recalculate. Check column A of Sheet5 for the recorded S255 value, then recalculate again and verify that a changed result is appended below it.

Use Worksheet_Calculate for recalculated formula results
Duplicate-check limits: The previous value is stored only in VBA memory, so it resets when the VBA project is reset or the workbook is reopened. Error values in S255 are skipped, and identical consecutive results are not logged even if recalculation produced them independently.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility, making it easy to move everyday workbook work to WPS. It does not fix Excel's event behavior, and VBA support and macro compatibility can vary by version and platform.

Open and edit spreadsheets with formulas in WPS Spreadsheets.Compatible with Microsoft Excel XLS and XLSX formats.Use a familiar interface for a smoother transition from Microsoft Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't Worksheet_Change run when a formula recalculates?

Worksheet_Change responds to changes made to cells, not changes in formula results caused by recalculation. Put a Worksheet_Calculate procedure in the source worksheet's code module to respond to recalculation.

Where should I paste the event code?

In Excel, right-click the tab of the worksheet containing S255, select View Code, and paste the procedure into that worksheet's module. Event procedures in a standard module will not run as worksheet events.

Will the calculation event record every randomized result?

The example records the first observed value and later values that differ from the last recorded value. Consecutive identical results are skipped, even if they came from separate recalculations.

What happens if column A on Sheet5 already has data?

The code finds the last nonempty cell in column A and writes the result in the row below it. If the column is empty, it starts at A1; it does not fill gaps above the last entry.