Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Second Find after a first find fails
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Second Find after a first find fails
You need to use datevalue
Option Explicit Sub FindCloseDateTST() Dim DateMinusOne As Date Dim DatePlusOne As Date Dim Found As Boolean Dim c As Variant DateMinusOne = DateValue("6/11/2009") DatePlusOne = DateValue("6/13/2009") Found = True Set c = Range("A1:A5").Find(What:=DateMinusOne, _ LookIn:=xlFormulas, _ LookAt:=xlWhole) If c Is Nothing Then Set c = Range("A1:A5").Find(What:=DateMinusOne, _ LookIn:=xlFormulas, _ LookAt:=xlWhole) If c Is Nothing Then Found = False End If End If '""""""""""""""""""""""""""""""""""""""""""""""""" """""""""""""""""""" If Found = False Then MsgBox "All OK" End If End Sub "MichaelDavid" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Second Find after a first find fails
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 -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Second Find after a first find fails
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
Find method fails in hidden range | Excel Programming | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
Find instruction fails (from foreign sheet) | Excel Programming |