3

I am comparing two Arrays in VBA for Excel 2010. Here is my sample code:

Dim vArray1 As Variant
Dim vArray2 As Variant

Set wb1 = ActiveWorkbook
Set myTable = wb1.Worksheets(3).ListObjects("Table3")

vArray1 = myTable.DataBodyRange
vArray2 = wb1.Worksheets(2).Range("B1:B" & lRow1).Value

k = 1

For i = LBound(vArray1) To UBound(vArray1)
   For j = LBound(vArray2) To UBound(vArray2)
      If vArray1(i, 1) = vArray2(j, 1) Then
         ' Do nothing
      Else
         vArray3(k, 1) = vArray1(i, 1)
         k = k + 1
      End If
   Next
Next

I want to do a comparison of Column 1 in Table 3 with the range stored in vArray2.

Any value that is present in vArray1 but not present in vArray2 needs to be stored in vArray3. Unfortunately, I am cannot get this done. Any assistance would be appreciated.

Improve this question

3 Answers 3

Reset to default
11

Edit1: I've re-written your loop a bit which is the cause of the problem I think. Ubound and Lbound assumes the first dimension if it is not supplied. So the way you do it and below should return the correct upper and lower bounds. But of course, it is better to be explicit when you're dealing with 2D arrays. Also vArray3 should be Dimensioned. I didn't see it in your code. Also added a Boolean variable.

ReDim vArray3 (1 to 10, 1 to 2) '~~> change to suit
Dim dup As Boolean: k = 1
For i = LBound(vArray1, 1) To UBound(vArray1, 1) '~~> specify dimension
    dup = False
    For j = LBound(vArray2, 1) To UBound(vArray2, 1) '~~> specify dimension
        If vArray1(i, 1) = vArray2(j, 1) Then
            dup = True: Exit For
        End If
    Next j
    If Not dup Then '~~> transfer if not duplicate
        vArray3(k, 1) = vArray1(i, 1)
        k = k + 1
    End If
Next I

Or you can use match like this:

'~~> Use 1D array instead by using Transpose
vArray2 = Application.Transpose(wb1.Worksheets(2).Range("B1:B" & lRow1))
For i = LBound(vArray1, 1) To UBound(vArray1, 1) '~~> specify dimension
    If IsError(Application.Match(vArray1(i, 1), vArray2, 0)) Then
        vArray3(k, 1) = vArray1(i, 1)
        k = k + 1
    End If
Next i
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

This code is checking for equality between two arrays, varArray1 and varArray2. The Join function is used to concatenate the elements of each array into a single string separated by commas.

Then, the two resulting strings are compared using the "=" operator to check if they are identical.

If they are, the variable IsEqual is set to True, indicating that the arrays are equal. If they are not equal, the code does not modify the value of IsEqual, so it remains False (or whatever value it had previously).

If (Join(varArray1, ",") = Join(varArray2, ",")) Then
    IsEqual = True
End If
Improve this answer

1 Comment

Please add a description about your code. Not just only code!
0

I have struggled with the algorithm comparing 2 arrays as well. I borrowed some ideas from the discussion here. Ultimately, I developed my own algorithm. I would like to share it with the community. I made some notes in the body of the algorithm. Further, I provided extended explanation. I hope somebody will find my way of thinking helpful.

Sub Comparing_2_Arrays()
          Dim arrayOSV6001() As Variant          
          Dim arrayPayTerms() As Variant          
          Dim arrayContracts() As Variant          
          Dim i As Integer          
          Dim j As Integer          
          Dim k As Integer          
          Dim l As Integer          
          Dim intRowsOSV6001 As Integer          
          Dim intRowsPayTerms As Integer          
          Dim m As Variant          
          Dim n As Variant

          ' Comment line 0:           
          l = 1
          k = 1

          ' Comment line 1
          ReDim arrayOSV6001(1 To intRowsOSV6001)
          ReDim arrayPayTerms(1 To intRowsPayTerms)
          ReDim arrayContracts(1 To 1)

          ' Comment line 2
          For i = 1 To UBound(arrayOSV6001)
          ' Comment line 3
                    For j = 1 To UBound(arrayPayTerms)
                              m = arrayOSV6001(i, 1)
                              n = arrayPayTerms(j, 1)
          ' Comment line 4
                              If m <> n Then
                                        k = k + 1
                              Else
                                        k = k + 0
                              End If
                    Next j
         ' Comment line 5
                    If k = j Then
                              arrayContracts(l) = m
         ' Comment line 6
                              k = 1
                              l = l + 1
                              ReDim Preserve arrayContracts(1 To l)
                    Else
                              k = 1
                    End If
          Next i
          ' Comment line 7
          If l = 1 Then
                    MsgBox "Missing and unique elements haven't been found."
                    Exit Sub
          Else
          ' Comment line 8
                    l = l - 1
                    ReDim Preserve arrayContracts(1 To l)
          End If
End Sub

Variable definition

arrayOSV6001 - array for contracts from general ledger
arrayPayTerms - array for contracts from payment terms register
arrayContracts - array for unique contracts missing in arrayPayTerms, but existing in arrayOSV6001
i - counter variable for elements of array of arrayOSV6001
j - counter variable for elements of array of arrayPayTerms
k - counter variable for the number of the elements of arrayPayTerms that are NOT equal to the elements of arrayOSV6001
l - variable for determining dimension of arrayContracts
intRowsOSV6001 - variable for determining dimension of arrayOSV6001
intRowsPayTerms - variable for determining dimension of arrayPayTerms
m -variable for values of the elements of arrayOSV6001
n - variable for values of the elements of arrayPayTerms

Comment lines

Comment line 0: Define initial values of the variables: l and k

Comment line 1: Define initial dimensions of the arrays: arrayOSV6001, arrayPayTerms, arrayContracts.
intRowsOSV6001 equals to the number of contracts downloaded from the general ledger.
intRowsPayTerms equals to the number of contracts downloaded from the payment terms register.

Comment line 2: Launch the cycle to examine the values of the elements of arrayOSV6001

Comment line 3: Launch the cycle to examine the values of the elements of arrayPayTerms

Comment line 4: Compare the values of arrayOSV6001 and arrayPayTerms in couples, then increase the K counter by one if the values are NOT similar.

Comment line 5: If the number of the elements of arrayPayTerms that are NOT equal to an element of arrayOSV6001equals to the number of iterations in the J cycle, it implies that a unique element of arrayOSV6001 has been found. Then record the found unique element in arrayContracts.

Comment line 6: Decrease the K counter to 0 and increaze the dimension of arrayContracts by 1 for the next unique element.

Comment line 7: If the dimension of arrayContracts hasn't changed after the L and J cycles have finished, it means that the unique elements missing in arrayPayTerms and existing in arrayOSV6001 haven't been found. Then exit from the sub procedure.

Comment line 8: If the dimension of arrayContracts has changed after the L and J cycles have finished, then redimension arrayContracts and eliminate the last empty element from arrayContracts that have been created for the next unique element of arrayOSV6001

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.