J
JonR
I have a function in a duty roster spreadsheet which uses the find method to
find the word "Holiday" and determine who should pull duty on that day. Code
is below.
It seems to work ok until it goes on to the Set d= .FindNext(d) line. Then
I get an Object Variable or With Block Variable Not Set error.
One other called function (Step_2) uses the Find method. Would this be
re-setting the With block? How can I prevent this, or get around it? Should
I be using subs instead of functions?
There are three worksheets, the Master, a staff sheet, and a sheet that has
everyone's vacation days.
code below.
--
TIA
JonR
-------------------------------------------------------------------------------------
Function Assign_Holiday()
Worksheets("DCSIS Master").Activate
Cells(1, 3).Activate
inX1 = ActiveCell.End(xlDown).Row 'finds the first empty slot inthe duty
roster,
'after the first empty slot, first holidays are populated, the weekends,
then the rest
'the "weekend" code is suffering the same problem
With Worksheets("DCSIS Master").Range(Cells(inX1, 2), Cells(inEnd, 2))
Set d = .Find("HOLIDAY", LookIn:=xlValues)
If Not d Is Nothing Then
firsthAddress = d.Address
Do
blHoliday = True
inRow = d.Row
dtDate = Cells(inRow, 1).Value
If Cells(inRow - 1, 2).Value = "WEEKEND" Then ' check for
weekend holiday
blWeekend = True
End If
Do Until blAvail = True
Step_2 'looks for the next available person
Loop
Step_3 ' populates the duty roster
blAvail = False
Set d = .FindNext(d)
Loop While Not d Is Nothing And d.Address <> firsthAddress
End If
End With
End Function
--------------------------------------------------------------------------------------------
find the word "Holiday" and determine who should pull duty on that day. Code
is below.
It seems to work ok until it goes on to the Set d= .FindNext(d) line. Then
I get an Object Variable or With Block Variable Not Set error.
One other called function (Step_2) uses the Find method. Would this be
re-setting the With block? How can I prevent this, or get around it? Should
I be using subs instead of functions?
There are three worksheets, the Master, a staff sheet, and a sheet that has
everyone's vacation days.
code below.
--
TIA
JonR
-------------------------------------------------------------------------------------
Function Assign_Holiday()
Worksheets("DCSIS Master").Activate
Cells(1, 3).Activate
inX1 = ActiveCell.End(xlDown).Row 'finds the first empty slot inthe duty
roster,
'after the first empty slot, first holidays are populated, the weekends,
then the rest
'the "weekend" code is suffering the same problem
With Worksheets("DCSIS Master").Range(Cells(inX1, 2), Cells(inEnd, 2))
Set d = .Find("HOLIDAY", LookIn:=xlValues)
If Not d Is Nothing Then
firsthAddress = d.Address
Do
blHoliday = True
inRow = d.Row
dtDate = Cells(inRow, 1).Value
If Cells(inRow - 1, 2).Value = "WEEKEND" Then ' check for
weekend holiday
blWeekend = True
End If
Do Until blAvail = True
Step_2 'looks for the next available person
Loop
Step_3 ' populates the duty roster
blAvail = False
Set d = .FindNext(d)
Loop While Not d Is Nothing And d.Address <> firsthAddress
End If
End With
End Function
--------------------------------------------------------------------------------------------