chrissnead
New Member
- Joined
- Apr 10, 2018
- Messages
- 16
I am trying to set an open workbook as variable book2 but keep getting a 'Subscript out of range' error. Below is the code I am working with. It errors at 'Set book2'. It's completely random and will work sometimes but most of the time it doesn't. Could someone help?
Set book1 = ActiveWorkbook
Set book2 = Workbooks("MySpreadsheet.xlsx") ' errors out here
Set lookFor = book1.Sheets(1).Cells(NextRow, 2) ' value to find
Set srchRange = book2.Sheets(1).Range("A:N") 'source
lookFor.Offset(0, 6).Value = Application.VLookup(lookFor, srchRange, 11, False)
lookFor.Offset(0, 7).Value = Application.VLookup(lookFor, srchRange, 10, False)
Set book1 = ActiveWorkbook
Set book2 = Workbooks("MySpreadsheet.xlsx") ' errors out here
Set lookFor = book1.Sheets(1).Cells(NextRow, 2) ' value to find
Set srchRange = book2.Sheets(1).Range("A:N") 'source
lookFor.Offset(0, 6).Value = Application.VLookup(lookFor, srchRange, 11, False)
lookFor.Offset(0, 7).Value = Application.VLookup(lookFor, srchRange, 10, False)