VBA Workbook/Worksheet Run-time error '9': Subscript out of range

Question

I am trying find matches in the A columns of two worksheets in two separate workbooks but I am coming across the above runtime error with the line "Set wb1 = Workbooks(w1)" highlighted, what am I doing wrong? I've been trying to get this going for a couple of days (insert forehead slap emoji lol) so any advice will be greatly appreciated.

Dim w1 As String, w2 As String
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim cl As Range, ws1Rng As Range, ws2Rng As Range, aCell As Range
Dim lRowW1 As Long, lRowW2 As Long
Dim lastRowWb1 As Long
Dim lastRowWb2 As Long
Dim aVal As Object
Dim bVal As Object

w1 = "C:\Users\Desktop\Scrapes\" & x & ".xlsm"
w2 = "C:\Users\Desktop\Scrapes\" & y & ".xlsm"

Set wb1 = Workbooks(w1)
Set wb2 = Workbooks(w2)
Set ws1 = wb1.Sheet1("NewHighs")
Set ws2 = wb2.Sheet1("NewHighs")

lastRowWb1 = ws1.Range("A1").CurrentRegion.Rows.Count
lastRowWb2 = ws2.Range("A1").CurrentRegion.Rows.Count

For Each aVal In ws1.Range("A2", ws1.Range("A" & lastRowWb1))
    For Each bVal In ws2.Range("A2", ws2.Range("A" & lastRowWb2))
        If aVal = bVal Then
            MsgBox aVal
        End If
    Next
Next

Is the workbook that you are trying to assign to `wb1` open? Also -- what are `x` and `y`? — John Coleman, Jul 23 '20 at 10:35
x and y is the file name that changes everytime I run the code. It's scraping a website and all of that side is running fine — HukaBee89, Jul 23 '20 at 10:38
Do a `Debug.Print w1` to see what the full path and name is - you might be adding ".xlsm" to a file that already has an ".xlsm" extension. — Applecore, Jul 23 '20 at 10:41

Gustav · Answer 1 · 2020-07-23T10:56:42.563

0

You probably need object names:

Set wb1 = Workbooks.Open(w1, False, False)
Set wb2 = Workbooks.Open(w2, False, False)
Set ws1 = wb1.Worksheets("NewHighs")
Set ws2 = wb2.Worksheets("NewHighs")

edited Jul 23 '20 at 10:56

answered Jul 23 '20 at 10:52

Gustav

53,498
7
29
55

Ok I tried that suggestion and its now giving a 'method or data member not found' error. Played around with different combos of that line but still getting the same – HukaBee89 Jul 23 '20 at 11:10

VBA Workbook/Worksheet Run-time error '9': Subscript out of range

1 Answers1