How to capture Addition and Subtraction value

Sunil

I think there are two problems with your reduced file.

1) In your sub MyMacro, the second call on sub RecordAddSub sends an incorrect parameter- corrected and shown in bold below

For R = NwsFirstRow To Rl

      Ra = R - NwsFirstRow + 1

      RecordAddSub Arr(Ra, NwsAvg1), Arr(Ra, NwsAdd1), .Cells(R, NwsAdd1), True

      RecordAddSub Arr(Ra, NwsAvg1), Arr(Ra, NwsAdd1), .Cells(R, NwsSub1), False

2) In your new sub RecordAddSub, you have these lines:

        If Not IsEmpty(OldVal) Then

            If IsAdd Then

                NewVal = WorksheetFunction.Add(NewVal, OldVal)

            Else

                NewVal = WorksheetFunction.Sub(NewVal, OldVal)

but there no worksheet functions called Add or Sub in standard Excel (so VBA throws up the error you mention). I changed the lines to this, which seems to work:

If Not IsEmpty(OldVal) Then
            If IsAdd Then
                NewVal = NewVal + OldVal
            Else
                NewVal = NewVal - OldVal
            End If

EXTRA ANSWER (to revised question)

In the attached version of your file, I've removed the failed sub RecordAddSub and performed the hisorical stock movement sums in MyMacro.

I've added some new constants and tidied it up so it's now (changes in bold):

Enum Nws                                        ' worksheet navigation (Sheet1)
    ' 267 (ex 206)then modified 29 Sept 2021
    NwsFirstRow = 2  ' change to suit 3 (row 3)
    NwsStock1 = 3    ' change to suit (3 =column C)
    NwsAdd1 = 4      ' change to suit (4 =column DE)
    NwsSub1 = 5      ' change to suit (5 =column E)
    NwsLast1 = 6     ' change to suit (5 =column F)
End Enum

Then I've added a hidden column F in Sheet1 since I can't be sure when your RTD runs (or use a Worksheet_Change event macro) so MyMacro saves the last stock value there whenever it is run. The new commented code is:

Sub MyMacro()
    ' 267 (ex 206 - 17 Jun 2021) then modified 30 Sept 2021

Dim Rl          As Long                 ' last used row in column A
Dim Arr         As Variant              ' read data from the worksheet
'Dim R           As Long                 ' loop counter: sheet rows
Dim Ra          As Long                 ' array row number

'Macro code that you want to run.

With ThisWorkbook.Worksheets("Sheet1")

    Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
    'write range to array for speed
    Arr = .Range(.Cells(NwsFirstRow, 1), .Cells(Rl, NwsLast1)).Value 'extended to included Last value


    For Ra = 1 To UBound(Arr, 1) ' loop through array
        If Arr(Ra, NwsStock1) >= Arr(Ra, NwsLast1) Then 'compare new and last stock
            'if >= last, add change to positive history cell
            Arr(Ra, NwsAdd1) = Arr(Ra, NwsAdd1) + Arr(Ra, NwsStock1) - Arr(Ra, NwsLast1)
            Else
            'if < last, add change to positive history cell
            Arr(Ra, NwsSub1) = Arr(Ra, NwsSub1) + Arr(Ra, NwsLast1) - Arr(Ra, NwsStock1)
        End If
        ' copy new stock in last stock cell (for next run)
        Arr(Ra, NwsLast1) = Arr(Ra, NwsStock1)

    Next Ra

    'write array back to same range
    .Range(.Cells(NwsFirstRow, 1), .Cells(Rl, NwsLast1)) = Arr

End With

    ' enable this line of you want to save the change:-
    'ThisWorkbook.Save

    'Calls the timer macro so it can be run again at the next interval.
'    Call SetTimer
End Sub

[Now if you manually change the values in column C of Sheet1  (or your RTD update does that) whenmyMacro is run, the differences will appear in columns D and E (what you called Addition and Subtraction).

(Note I realised that counter R is now not required (since the code loops only through the array) so its Dim declaration statement is commented out.

In your production situation, you'll need to call the macro via timers (as we've shown before). When you clear values in Sheet1, don't forget to include column F. Given all the work is done in the array (apart from writing back to the cells once), I've also removed the lines turning Application.ScreenUpdating off and on 

Hope this finally fixes things for you.

.Range(.Cells(NwsFirstRow, 1), .Cells(Rl, NwsLast1)) = Arr()

Arr(Ra, NwsAdd1) = Arr(Ra, NwsStock1) - Arr(Ra, NwsLast1)

Arr(Ra, NwsAdd1) = Arr(Ra, NwsAdd1) + Arr(Ra, NwsStock1) - Arr(Ra, NwsLast1)

        
    'write array back to same range
    .Range(.Cells(NwsFirstRow, 1), .Cells(Rl, NwsLast1)) = Arr
    'add time to stock header
    .Cells(1, NwsStock1) = "Day factory stock (at " & Time & ")"