How to set another workbook as a variable

[chrissnead]

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)

 

[chrissnead]

If I use the below it gives me run time error 9 Subscript out of range.

Set book1 = ActiveWorkbook
Set book2 = Workbooks("MySpreadsheet.xlsx")

If I take the route you suggested of doing the below it gives the same error, but this time at the .Activate.

Set book1 = ActiveWorkbook
Workbooks("MySpreadsheet.xlsx").Activate
Set book2 = ActiveWorkbook

Here's what I've got so far:




Sub getInfo()


Dim lookFor As Range
Dim srchRange As Range
Dim book1 As Workbook
Dim book2 As Workbook


Set book1 = ActiveWorkbook
Set book2 = Workbooks("MySpreadsheet.xlsx")


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)
lookFor.Offset(0, 8).Value = "VISALIA"
lookFor.Offset(0, 9).Value = Application.VLookup(lookFor, srchRange, 3, False)
If lookFor.Offset(0, 9).Value = "RETURNED" Then
lookFor.EntireRow.Copy
book1.Sheets(3).Range("B2").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.PasteSpecial
lookFor.Select
End If
lookFor.Offset(0, 10).Value = Application.VLookup(lookFor, srchRange, 13, False)
lookFor.Offset(0, 11).Value = Application.VLookup(lookFor, srchRange, 14, False)
lookFor.Offset(0, 12).Value = lookFor.Offset(0, 10).Value + 1

End Sub

 

[Joe4]

What if you change this line:

[COLOR=#333333]Workbooks("MySpreadsheet.xlsx").Activate[/COLOR]

to this:

[COLOR=#ff0000]Windows[/COLOR][COLOR=#333333]("MySpreadsheet.xlsx").Activate[/COLOR]

Does that work for you?

 

[chrissnead]

No, it now says Run-time error '13' Type mismatch

 

[Joe4]

It sounds to me like those two files may not really be in the same Excel session.
If you go into the VB Editor, and view the VB Explorer in the left-column, do you see BOTH workbooks listed there?
If not, then they are NOT both in the same Excel session.

 

[chrissnead]

I was actually able to get it working finally by using a couple of your tips but declaring a third workbook variable.

Set book1 = ActiveWorkbook
Set book2 = Windows("JB Hunt- FX Express.xlsx")

book2.Activate

Set book3 = ActiveWorkbook

book1.Activate

Thanks Joe4 for the help!