Second Find after a first find fails
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
|