Second Find after a first find fails

[MichaelDavid]

Greetings! On an Excel Worksheet, A1 thru A5 contain:
6/1/2009
6/2/2009
6/3/2009
6/4/2009
6/12/2009

The following code runs perfectly. But when I uncomment the commented
Selection.Find instruction, I get the following
error message on execution:

"Run-time error '91':
Object variable or With block variable not set."

and the following instruction (at line 22) is highlighted:
Selection.Find(What:=DatePlusOne, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

The code is as follows:

Option Explicit
Sub FindCloseDateTST()
Dim DateMinusOne As Date
Dim DatePlusOne As Date

DateMinusOne = "6/11/2009"
DatePlusOne = "6/13/2009"

Range("A1:A5").Select

On Error GoTo CheckDateP1

' Selection.Find(What:=DateMinusOne, LookIn:=xlFormulas _
' , LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
' MatchCase:=False, SearchFormat:=False).Activate

'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
CheckDateP1:
Range("A1:A5").Select
On Error GoTo DateNotFound

' And here is Line 22:
Selection.Find(What:=DatePlusOne, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

DateNotFound:

MsgBox "All OK"

End Sub

--
Thank you very much for your help. Suggestions and work-arounds will be
greatly appreciated. May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[OssieMac]

Hi Michael,

Your code will be a lot better if you write it like the following so there
is no need to use On Error and you can test for whether the value was found
and direct the code accordingly.

Note the way I have inserted the dates enclosing them in # signs so that
they are actual dates (not strings) and DateValue not required to convert
them to dates.

If you need more explanation of what the code is doing then get back to me
and tell me what you don't understand and I will attempt to explain in detail.

Sub FindCloseDateTST()
Dim DateMinusOne As Date
Dim DatePlusOne As Date

Dim rngToFind As Range
Dim rngToSearch As Range

DateMinusOne = #6/11/2009#
DatePlusOne = #6/13/2009#

'Edit Sheet1 to match your sheet name
With Sheets("Sheet1")
Set rngToSearch = .Range("A1:A5")
End With

Set rngToFind = rngToSearch _
.Find(What:=DateMinusOne, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If rngToFind Is Nothing Then
'Not found so second search
Set rngToFind = rngToSearch _
.Find(What:=DatePlusOne, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
End If

If rngToFind Is Nothing Then
'Not found in either search
MsgBox "All OK"
Else
MsgBox "Found " & rngToFind.Value
End If

End Sub

 

[MichaelDavid]

Hi OssieMac:

Thanks for your kind help. I used your suggestion and successfully succeeded
in debugging my macro. (The complete macro has more than 180 lines of code.)
The purpose of the macro is to make suggestions as to how an invalid price
(in Column M) should be changed by looking at other prices up to a few days
before and after the date of the invalid price. (Dates are in Column H and
Prices are in Column M.) One first highlights (selects) the invalid price on
the worksheet (in Column M). Then one does a Ctl-l to activate the macro. On
completion of the macro, a display indicates other prices up to a few days
before and after the date of the selected price, and the Macro exits with the
invalid price (in Column M) highlighted so that it can be quickly changed.
Here is a portion of the code in which I used your suggestion:

ACA = Application.ActiveCell.Address
ActvCellRow = Application.ActiveCell.Row
ActvCellCol = Application.ActiveCell.Column

If ActvCellCol <> 13 Then
MsgBox "Only selections in Column M are allowed"
Exit Sub
End If

Cells(ActvCellRow, ActvCellCol - 5).Select

DateOK = IsDate(ActiveCell.Value)
If Not DateOK Then
MsgBox "Invalid Date! Please try again"
Range(ACA).Select
Exit Sub
End If

ActvCellRow = Application.ActiveCell.Row
ActvCellCol = Application.ActiveCell.Column

ActvCellContents = Application.ActiveCell.Value
SvdActvCellContents = ActvCellContents
SvdActvCellRow = ActvCellRow
SvdActvCellCol = ActvCellCol

Cells(ActvCellRow, ActvCellCol) = #1/1/1900#
DateMinusTwo = ActvCellContents - 2
DateMinusOne = ActvCellContents - 1
SameDate = ActvCellContents
DatePlusOne = ActvCellContents + 1
DatePlusTwo = ActvCellContents + 2

LstRowData = Range("O2")

Range("H8:H" & LstRowData).UnMerge

With ActiveSheet
Set rngToSearch = .Range("H8:H" & LstRowData)
End With

CheckDateM2:

Set rngToFindM2 = rngToSearch _
..Find(What:=DateMinusTwo, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If rngToFindM2 Is Nothing Then
MessageDateM2 = ""
Else
MessageDateM2 = "On Row " & rngToFindM2.Row & ", found Date M2 = " &
rngToFindM2.Value & "; Price Date M2 = " & Cells(rngToFindM2.Row,
rngToFindM2.Column + 5)
End If

--
Once again many thanks. May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

Hi Joel:
Thank you very much for your kind help. Your suggestion is very similar
to OssieMac's suggestion. With both suggestions very similar, I used them in
successfully debugging my macro. (The complete macro has more than 180 lines
of code.) The purpose of the macro is to make suggestions as to how an
invalid price (in Column M) should be changed by looking at other prices up
to a few days before and after the date of the invalid price. (Dates are in
Column H and Prices are in Column M.) One first highlights (selects) the
invalid price on the worksheet (in Column M). Then one does a Ctl-l to
activate the macro. On completion of the macro, a display indicates other
prices up to a few days before and after the date of the selected price, and
the Macro exits with the invalid price (in Column M) highlighted so that it
can be quickly changed. Here is a portion of the code in which code suggested
by both of you was used:

ACA = Application.ActiveCell.Address
ActvCellRow = Application.ActiveCell.Row
ActvCellCol = Application.ActiveCell.Column

If ActvCellCol <> 13 Then
MsgBox "Only selections in Column M are allowed"
Exit Sub
End If

Cells(ActvCellRow, ActvCellCol - 5).Select

DateOK = IsDate(ActiveCell.Value)
If Not DateOK Then
MsgBox "Invalid Date! Please try again"
Range(ACA).Select
Exit Sub
End If

ActvCellRow = Application.ActiveCell.Row
ActvCellCol = Application.ActiveCell.Column

ActvCellContents = Application.ActiveCell.Value
SvdActvCellContents = ActvCellContents
SvdActvCellRow = ActvCellRow
SvdActvCellCol = ActvCellCol

Cells(ActvCellRow, ActvCellCol) = #1/1/1900#
DateMinusTwo = ActvCellContents - 2
DateMinusOne = ActvCellContents - 1
SameDate = ActvCellContents
DatePlusOne = ActvCellContents + 1
DatePlusTwo = ActvCellContents + 2

LstRowData = Range("O2")

Range("H8:H" & LstRowData).UnMerge

With ActiveSheet
Set rngToSearch = .Range("H8:H" & LstRowData)
End With

CheckDateM2:

Set rngToFindM2 = rngToSearch _
..Find(What:=DateMinusTwo, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If rngToFindM2 Is Nothing Then
MessageDateM2 = ""
Else
MessageDateM2 = "On Row " & rngToFindM2.Row & ", found Date M2 = " &
rngToFindM2.Value & "; Price Date M2 = " & Cells(rngToFindM2.Row,
rngToFindM2.Column + 5)
End If

--
Once again many thanks. May you have a most blessed day!

Sincerely,

Michael Fitzpatrick