Macro for formula result loging/recording

[simas_r]

Dear all,

I need a little bit of help. I'm trying to find/make a VBA code which could log results from the formula in a table. After any variable change the formula results should be recorded in to the table and it should keep a last 10 records. After the table is filled up, the very last result should be deleted and the 10 recent results should be present. I have attached a sample xls file for beter understanding.Result loging.xlsx

[sktneer]

Hi,
Welcome to the forum.

Right Click on Sheet1 Tab --> View Code and place the code given below in the opened code window --> Save your workbook as Excel Macro-Enabled Workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
If Target.Count > 1 Then Exit Sub
If Target.Address(0, 0) = "F2" Or Target.Address(0, 0) = "H2" Then
        If Range("B17").Value = "" Then
                r = 17
                Cells(r, "B") = Cells(3, "B")
                Cells(r, "C") = Cells(r, "C")
        ElseIf Range("B17").End(xlUp).Row < 8 Then
                r = 16
                Cells(r, "B") = Cells(3, "B")
                Cells(r, "C") = Cells(r, "C")
        ElseIf Range("B17").End(xlUp).Row > 8 Then
                r = Range("B17").End(xlUp).Row - 1
                Cells(r, "B") = Cells(3, "B")
                Cells(r, "C") = Cells(3, "C")
        ElseIf Range("B17").End(xlUp).Row = 8 Then
                r = 8
                Range("B8:C16").Copy
                Range("B9").PasteSpecial xlPasteValues
                Cells(r, "B") = Cells(3, "B")
                Cells(r, "C") = Cells(r, "C")
        End If
End If
End Sub

This is a Worksheet_Change Event Code, so once you change the value in H2, that will change the values returned by the Formula1 and Formula2 and hence will trigger the code to place the formula results in the desired row in the table.

For detail see the attached.

[sktneer]

Glad that it is working as per your requirement. Thanks for the feedback.


If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.