Error Code 91 when multiple excel workbooks are open

[Archer47]

Hello All Any Help is appreciated

I am getting the following code when I run my program with multiple excel workbooks open.

Run-Time Error '91':

Object variable or with block variable not set.

If I open just the workbook with the code it will run fine but when I open up other workbooks it will fail. Also if I close all workbooks and then reopen the program it will work.

Here is my code
it will fail at this line TestRange2.Find(EmpNumber).Activate

Sub ProcessRequestOff()
' Author: 
' Description: This is used to change the availbilty on the TempServer sheet from the requestOff Form.
' ( changes only the approved request offs for the selected week)

Dim DateTemp, DateReqOff, DateTest As Date
Dim intReqCount(), intRow, TempRow, tempCol As Integer ' used to deterimine the number of request
Dim I As Integer ' Used for loops
Dim TestRange2, TestRange3 As Range
Dim strLastCell As String, intPos As Integer


Set WsTemp = Worksheets("tempServer")
Set WsReqOff = Worksheets("TempRequest")
WsTemp.Activate
WsTemp.Range("G1").Activate
DateTemp = ActiveCell.Value   ' Set the start range for dates to be processed
DateTest = DateTemp + 6       ' Sets the end range for dates to be processed
WsReqOff.Activate
WsReqOff.Range("A1").Activate
intRow = WsReqOff.Range("A65536").End(xlUp).Row - 2 ' determines the number of request(must subtract one because of first row and 1 more because of option base 0)
If intRow = -1 Then ' bypass the rest of the code if there are no request off's
Else
ReDim intReqCount(intRow)

For I = 0 To intRow
WsReqOff.Activate
WsReqOff.Range("A2").Offset(I, 0).Activate
EmpNumber = ActiveCell.Value
ActiveCell.Offset(0, 3).Activate
DateReqOff = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
intPos = ActiveCell.Value
If intPos = MainCounter Then                                ' only change the current position being processed
WsTemp.Activate
BoolAval = False                                            ' used to set availability to false
WsTemp.Range("A3").Activate
ActiveCell.End(xlDown).Activate
strLastCell = ActiveCell.Address
Set TestRange2 = Worksheets("tempserver").Range("A2", strLastCell)
Set TestRange3 = Worksheets("tempserver").Range("G1:M1")
TestRange2.Activate
TestRange2.Find(EmpNumber).Activate
TempRow = ActiveCell.Row
WsTemp.Range("F1").Activate
If DateReqOff >= DateTemp And DateReqOff <= DateTest Then    ' only change the current request offs
TestRange3.Find(DateReqOff).Activate
tempCol = ActiveCell.Column
Cells(TempRow, tempCol).Select                               ' select the correct cell
ActiveCell.Value = BoolAval                                  ' override value to "false"
Else
End If
Else
End If
Next
End If
End Sub

Any thoughts or help is appreciated.

Thanks
Dan

 

[Archer47]

Well I think I was able to fix the problem I was having. Hopefully this can help other people. I needed to set the find code to a range for it to work with multiple workbooks open.
This code fixed it so far.
had to add a new range
dim RangeFind as range

Set RangeFind = Nothing
Set RangeFind = TestRange2.Find(EmpNumber)

I set rangeFind to nothing first because the code can be run multiple times, not sure if that is needed or not.
Does anyone know?
Thanks
Dan

 

[Norie]

Dan

It shouldn't be needed but it shouldn't matter either, so there would be no harm in doing it.

By the way, one reason you are getting that error is that the find hasn't been successful.

If that happens Find returns nothing, and you can't Activate Nothing.

By using Set RangeFind = TestRange1.Find(EmpNuimber) as you have you can avoid the error by checking if the search was succesfult.

You can do that like this.

If RangeFind Is Nothing Then
      ' search failed
Else
      ' search successful
End If

Which is probably what you are doing anyway.

As for multiple workbooks, I can't see how that could be causing the problem.

Unless there is a worksheet with the same name as the one you want to search in one of the other workbooks.

Then TestRange2 might actually refer to the wrong workbook.

You can avoid that by adding a workbook reference to the workbook the the code is in when you set the range.

Set TestRange2 = ThisWorkbook.Worksheets("tempserver").Range("A2", strLAstCell)

 

[Archer47]

Thanks for the reply.
One thing that through me for a loop is that it should always find the (empNumber) because the (empNumber) will always be in the range. (for example: my code was still failing even though the (empnumber) was in the range.
From my research it looks like the range object is being "lost or deleted" when other workbooks are open. ( I guess I don't understand range objects enough at this point.)


I think your solution below will work and I'm going to try that out also.
Because I do think it has to do with the workbook reference.
Thanks
Dan

You can avoid that by adding a workbook reference to the workbook the the code is in when you set the range.

Set TestRange2 = ThisWorkbook.Worksheets("tempserver").Range("A2", strLAstCell)

 

[Norie]

Dan

What I was thinking that perhaps more than one workbook has a worksheet called 'TempServer'.

Then because of there being no workbook reference VBA is 'looking' at the wrong workbook.

I imagine it would be quite unlikely but it could happen and I can't see anything else that could be causing the problem.

I suppose opening a workbook while the code was running might mess things up but you don't seem to be doing that.