0

Script below currently stores an array and then compares it to an RTD updated array and outputs if there is a change. If there is no change then it will not log the change. Works great, but now I need an output log of the difference if a change occurs rather than output of a changed value in the updated array. enter image description here

Code in Module 1

 Public myArr()
 Public Sub 
 PopulateMyArr()
 myArr = Sheet4.Range("I6:I500").Value
 End Sub

Code in This Workbook

 Private Sub Workbook_Open()
 PopulateMyArr
 End Sub

Code in Sheet4(BA_Size)

 Private Sub ToggleButton1_Click()

 End Sub

 Private Sub Worksheet_Calculate()

 Dim keyCells As Range
 On Error GoTo safeexit
 Application.EnableEvents = False

 Set keyCells = Me.Range("I6:J500")

 If Worksheets("BA_Size").ToggleButton1.Value = True Then
 Dim i As Integer
 For i = 1 To UBound(myArr)
 If keyCells(i, 1).Value <> myArr(i, 1) Then
   
 nextrow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row + 1
 Sheet1.Cells(nextrow, "A").Value = Me.Cells(i + 5, "I").Value
 End If
 Next i
 
 End If
 safeexit:
 PopulateMyArr
 Application.EnableEvents = True
 End Sub
mjac
  • 125
  • 11
  • Are you just logging the difference of `keyCells(i, 1).Value` and `myArr(i, 1)` ? – Tim Williams Jun 30 '21 at 00:13
  • Yes, I believe this line of code is comparing the change. Then if there is a change say updated array is 17 and the stored array is 12 it would log 17. What I need it to output is 5. – mjac Jun 30 '21 at 00:18
  • So `myArr(i, 1) - keyCells(i, 1).Value` ? Did you try that? – Tim Williams Jun 30 '21 at 00:20
  • Let me check, I was assuming I would need to verify if a change occurred first then add more code to output the difference. I wasn't sure if the change and the difference could be done in the same line of code. – mjac Jun 30 '21 at 00:21
  • No that does not work it does the same thing as ``keyCells(i, 1).Value <> myArr(i, 1)`` – mjac Jun 30 '21 at 00:36
  • 1
    You already have the check for `If keyCells(i, 1).Value <> myArr(i, 1) Then` so all you need to do is edit the line `Sheet1.Cells(nextrow, "A").Value = Me.Cells(i + 5, "I").Value` to `Sheet1.Cells(nextrow, "A").Value = myArr(i, 1) - keyCells(i, 1).Value` – Tim Williams Jun 30 '21 at 00:56
  • You got it! It works. Thanks so much I didn't know I could add that function to that line of code which would perform the difference. – mjac Jun 30 '21 at 01:05
  • @TimWilliams any help w/ this ? is much appreciated it is similar. https://stackoverflow.com/questions/68230028/array-difference-if-or-vba-script – mjac Jul 02 '21 at 18:46

0 Answers0