Problem with Find method
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
--------------------------------------------------------------------------------------------
|