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
--------------------------------------------------------------------------------------------

Jon,

You can only use the FindNext with the conditions of the last time find was
used. Try using the full find method again instead of FindNext. I have found
that this works.

Also, check out the Find Method in Help. Click on Show All and in the
remarks well down the page you will see a comment like this:-

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each
time you use this method. If you dont specify values for these arguments the
next time you call the method, the saved values are used. Setting these
arguments changes the settings in the Find dialog box, and changing the
settings in the Find dialog box changes the saved values that are used if you
omit the arguments. To avoid problems, set these arguments explicitly each
time you use this method.

These arguments are even saved from a find done in the interactive mode and
therfore they should always be reset.

Regards,

OssieMac


"JonR" wrote:

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
--------------------------------------------------------------------------------------------

Jon,

Forgot something before. If you use the full find method instead of findnext
then you need to save the previous find address and set the after argument
like this:-

saveAddre = d.Address 'Set immediately after the first find

Set d = .Find(What:="jim", after:=Range(saveAddre), _
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

saveAddre = d.Address 'Set after each successive find

Regards,

OssieMac

"OssieMac" wrote:

Jon,

You can only use the FindNext with the conditions of the last time find was
used. Try using the full find method again instead of FindNext. I have found
that this works.

Also, check out the Find Method in Help. Click on Show All and in the
remarks well down the page you will see a comment like this:-

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each
time you use this method. If you dont specify values for these arguments the
next time you call the method, the saved values are used. Setting these
arguments changes the settings in the Find dialog box, and changing the
settings in the Find dialog box changes the saved values that are used if you
omit the arguments. To avoid problems, set these arguments explicitly each
time you use this method.

These arguments are even saved from a find done in the interactive mode and
therfore they should always be reset.

Regards,

OssieMac


"JonR" wrote:

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
--------------------------------------------------------------------------------------------

Thanks OssieMac

With your suggestions I was able to come up with a working solution
--
JonR


"OssieMac" wrote:

Jon,

Forgot something before. If you use the full find method instead of findnext
then you need to save the previous find address and set the after argument
like this:-

saveAddre = d.Address 'Set immediately after the first find

Set d = .Find(What:="jim", after:=Range(saveAddre), _
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

saveAddre = d.Address 'Set after each successive find

Regards,

OssieMac

"OssieMac" wrote:

Jon,

You can only use the FindNext with the conditions of the last time find was
used. Try using the full find method again instead of FindNext. I have found
that this works.

Also, check out the Find Method in Help. Click on Show All and in the
remarks well down the page you will see a comment like this:-

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each
time you use this method. If you dont specify values for these arguments the
next time you call the method, the saved values are used. Setting these
arguments changes the settings in the Find dialog box, and changing the
settings in the Find dialog box changes the saved values that are used if you
omit the arguments. To avoid problems, set these arguments explicitly each
time you use this method.

These arguments are even saved from a find done in the interactive mode and
therfore they should always be reset.

Regards,

OssieMac


"JonR" wrote:

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
--------------------------------------------------------------------------------------------