Excel VBA - Comparing Two Arrays in Two Workbooks

Question

I'm using two different worksheets in two different workbooks. I'm using data in the first worksheet to fill out the second worksheet.

I have two lists (call them x and y) in Workbook A, and two lists in Workbook B. I'm comparing list x in Workbook A and B. If any of the cells in WorkbookA-ListX match up with any of the cells in WorkbookB-ListX, I'll copy the value of the cell in WorkbookA-ListY to the cell in WorkbookB-ListY.

I'm having trouble comparing the original two lists. I am thinking about using the first value in WorkbookA-ListX and comparing it to the array WorkbookB-ListX to see if there are any matches, and then just increment WorkbookA-ListX.

The lists are basically a range of values in the same column, so B3:B53 for example is the size of the lists. I used this link to help me but I keep getting an error code saying "Run-time error 13, Type mismatch" when I get to the first "For iRow..." What should I do from here?

Private Sub CommandButton1_Click()

Dim wkb0 As Workbook
Dim wkb1 As Workbook

Dim varSheet0, varSheet1 As Variant
Dim RangeToCheck0, RangeToCheck1 As String
Dim iRow, iCol As Long

Set wkb0 = Workbooks.Open("P:\BEN\EMOP\Work Space\Congestion_May.xlsx")
Set varSheet0 = wkb0.Worksheets("Page1").Range("B3:B82,B86:B105")
Set wkb1 = Workbooks.Open("P:\BEN\EMOP\Work Space\Congestion_Apr_abr.xlsx")
Set varSheet1 = wkb1.Worksheets("Page1").Range("B3:B51,B54:B73")

nlin = 1
ncol = 1


For iRow = LBound(varSheet0, 1) To UBound(varSheet0, 1)
    For iCol = LBound(varSheet0, 2) To UBound(varSheet0, 2)
        If varSheet0(iCol, iRow) = varSheet1(iRow, iCol) Then
            varSheet0.Range("E" & iRow).Value = varSheet1.Range("E" & iRow).Value
        End If
    Next
Next

i think that having multiple areas in your range means that this problem does not lend itself to be solved with arrays — Marcucciboy2, Jun 08 '18 at 17:20
[You can iterate through a range rather easily](http://codevba.com/excel/for_each_cell_in_range.htm#.Wxq7U_nwaM8) without transferring it into an array — Marcucciboy2, Jun 08 '18 at 17:21
If you are set on using the array method above, then one thing to note is that you havent actually saved your values to an array. If you were to open your `Locals Window` you would find that `varSheet0` and `varSheet1` are actually both Ranges. The difficult part of the problem (imo) is that your ranges are not contiguous eg `B3:B82,B86:B105` — Marcucciboy2, Jun 08 '18 at 18:19

Shawn V. Wilson · Answer 1 · 2018-06-08T18:21:50.190

0

You're actually using ranges, not arrays. You declared Dim varSheet0, varSheet1 As Variant instead of Dim varSheet0() as Variant, varSheet1() As Variant

That said, you dont' really need arrays.

But I don't think you're comparing each value in WALX to the entire WBLX.

Would this work?

For iRow = 1 To varSheet0.Rows.Count
    iRowResult = ""
    On Error Resume Next
        'Where is varSheet0 value in varSheet1?
        iRowResult = WorksheetFunction.Match(varSheet0.Cells(iRow).Value, varSheet1, 0)
    On Error GoTo 0
    If iRowResult = "" Then
        'There's no match in varSheet1. Do what you need.
    End If
Next iRow

edited Jun 08 '18 at 18:21

answered Jun 08 '18 at 17:17

Shawn V. Wilson

1,002
3
17
42

if you're going to go all out and solve this, you might find [this question](https://stackoverflow.com/a/13247278/2727437) helpful. – Marcucciboy2 Jun 08 '18 at 17:21
I used this but my code still does not do what it should be doing. The first cell in the wkba-listx should be checked with the entire wkbb-listx, and if nothing matches it should increment the cell in the first list. It should be putting values into wkbb-listy from wkba-listy, but it is not outputting anything. I am not getting any compile or run-time errors, but nothing is happening with my code either. – Aqsa Ahsan Jun 08 '18 at 17:46
@AqsaAhsan Have you tried stepping through or putting a breakpoint on `varSheet0.Range("E" & iRow).Value = varSheet1.Range("E" & iRow).Value` to verify that the IF-statement gets met? Sometimes values that look the same to us are interpreted by the code as being different due to the inclusion of white-space characters. – Mistella Jun 08 '18 at 18:17
I believe that `If varSheet0.Cells(iRow).Value = varSheet1.Cells(iRow).Value Then` wont line up correctly because the two ranges are not the same length and also do not start at the same index. You'll have to separately increment a second variable to evaluate `varSheet1.Cells()` – Marcucciboy2 Jun 08 '18 at 18:22
What do you mean by 'Where is varSheet0 value in varSheet1? – Aqsa Ahsan Jun 08 '18 at 19:51
@AqsaAhsan varSheet0.Cells(iRow) is a particular value in the varSheet0 list. My question means: "In what row of varSheet1 does that value exist, if any?" – Shawn V. Wilson Jun 08 '18 at 22:33

Excel VBA - Comparing Two Arrays in Two Workbooks

1 Answers1