CODEWITHSUNDEEP
vbaexcel
Aqsa Ahsan
Aqsa Ahsan

Reputation: 1

Excel VBA - Comparing Two Arrays in Two Workbooks

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

Upvotes: 0

Views: 414

Answers (1)

Shawn V. Wilson
Shawn V. Wilson

Reputation: 1111

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

Upvotes: 0

Related Questions