Array Difference IF/OR VBA Script

Question

Have script that stores an array and if it changes it finds the difference. Now I need it to take that difference and check against if the adjacent cell is "John or Mary" and times the array value difference by: (If "John" = Array Value Diff. * PO#1) OR (If "Mary" = Array Value Diff. * PO#2). This is the final value I need written to Sheet 1 which right now is just the Array Value Diff. value.

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

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:I500")

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 = (keyCells(i, 1).Value - myArr(i, 1))
End If
Next i
End If
safeexit:
PopulateMyArr
Application.EnableEvents = True
End Sub

How the column J:J (Array difference) is populated? I do not see anything in your code. And your code writes the differences in `Sheet1`, but with which purpose? You cannot use this records because there are records only for differences and you cannot make any match between one of the used arrays and this records... Am I missing anything from your question? — FaneDuru, Jul 02 '21 at 19:08
The array difference is calculated with ``(keyCells(i, 1).Value - myArr(i, 1))`` and the value is written in ``Sheet 1``. What I need it to do is write in ``Sheet 1`` if John - Array Value Diff. * PO#1 or if Mary - Array Value Diff. * PO#2.....do I need to capture a bigger array to add in the "IF" logic? — mjac, Jul 02 '21 at 19:16
You show us a picture with some data in columns. Are they real data or only your imagination? If real data, should we understand that **all the time are differences between the initial array and the one obtained after calculation**? I can show you how to create another array keeping what you describe, but how to correlate it with the existing situation? I asked you about the values in J:J, but you did not answer anything... — FaneDuru, Jul 02 '21 at 19:33
Real data. Yes, for instance in the first row column I it showing 86 in the picture but with a difference of 10 in column J. Therefore, the original value in column I was 76 maybe a second ago.......and so on down the rows of columns I&J. The difference in the code works great. What I need is as the differences are constantly being calculated to take those differences and * them by the PO#s based up if it is John or Mary. — mjac, Jul 02 '21 at 19:40
I asked you how the differences from column J:J are recorded in this column. — FaneDuru, Jul 02 '21 at 19:42
The picture showing the values recorded in J:J is just for illustration the actual "J:J" values are recorded in ``Sheet1.Cells(nextrow, "A").Value=`` — mjac, Jul 02 '21 at 19:44
I am afraid, we are speaking two different languages. I am not referring to English... I cannot understand enough to try helping you, sorry... I could see that the differences are recorded in sheet1, but I **specifically asked about J:J in your picture**. Did you receive the picture from somebody else...? If not, you should be able to answer my clarification questions. If not, I am afraid that I cannot help you. — FaneDuru, Jul 02 '21 at 19:48

score 1 · Accepted Answer · answered Jul 02 '21 at 20:03

Untested:

Private Sub Worksheet_Calculate()

    Dim keyCells As Range, i As Long, diff, cKey As Range
    
    'exit if togglebutton not on
    If Not Worksheets("BA_Size").ToggleButton1.Value Then Exit Sub
    
    On Error GoTo safeexit
    Application.EnableEvents = False
    
    Set keyCells = Me.Range("I6:I500")
    nextrow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row + 1
    
    For i = 1 To UBound(myArr)
        Set cKey = keyCells(i, 1)
        If cKey.Value <> myArr(i, 1) Then
            diff = (cKey.Value - myArr(i, 1))
            'check value in Col K
            Select Case cKey.EntireRow.Columns("K").Value
                Case "John": diff = diff * cKey.EntireRow.Columns("N").Value
                Case "Mary": diff = diff * cKey.EntireRow.Columns("O").Value
                Case Else: diff = 0
            End Select
            Sheet1.Cells(nextrow, "A").Value = diff
            nextrow = nextrow + 1
        End If
    Next i
      
safeexit:
    PopulateMyArr
    Application.EnableEvents = True
End Sub

https://stackoverflow.com/questions/69000686/multiple-array-calculations — mjac, Aug 31 '21 at 19:44

Array Difference IF/OR VBA Script

1 Answers1

Linked