VBA cannot find a date

Question

I have a row ( Row 1) of many cells.  The first cell is a date.  Each succesive 6th cell is a formula that references the

6th cell to the left and simply adds 1 to that cell.  The result is a string of consecutive dates, 6 cells apart.  The statement

Set rStartDate = Range("1:1").Find(What:=StartDate)

cannot find any date except the first one.  I get the error "Object variable not set."  What am I missing?  Thanks for your time.  Otto

Answer 1

Rick

I appreciate your patience with my problem.  Here is what I made to show you the problem.  I start a new blank wb.  In A1 I place the header "FormulaDates".  In C1, the header "RealDates".  In A2 I enter the date 1-Mar-11.  In A3 I enter the formula =A2+1.  Using the fill handle I drag this formula down to A6.  I now show dates from 1-Mar-11 to 5-Mar-11 in A2:A6.  In C2 I enter 1-Mar-11.  Using the fill handle (no formulas) I drag this date down to C6.  C2:C6 now shows the same dates as A2:A6.  I name the respective date ranges by their headers.

In B1 I enter 3-Mar-11 (the date I'm searching for.)

I write the following code:

Sub FindDate()

    Dim SearchDate As Date, rSearchRng As Range, rFoundCell As Range

    SearchDate = Range("B1")

    'Set rSearchRng = Range("FormulaDates")

    Set rSearchRng = Range("RealDates")

    Set rFoundCell = rSearchRng.Find(What:=SearchDate)

    MsgBox rFoundCell.Address(0, 0)

End Sub

Note that there are 2 lines that start with "Set SearchRng".  You remark out one of them so that you are searching either "FormulaDates" or "RealDates".  If you search "FormulaDates", the Msgbox line will error out with "Object variable or With block variable not set".  If you search "RealDates" you get C4.  My question is why is that error produced?  I again thank you for your time.  Your time is the most valuable thing you can give me.  Otto

Answer 2

Thanks for your reply.  rStartDate is a range and StartDate is a Date value.  I am searching a range of dates to find the cell that holds the date.  I have done some small tests and I have found that VBA will not find a date in a range if that date is in its cell by formula.  That was a surprise.  I went back to my original file and wrote some simple loop code to enter all the dates (about 400 consecutive dates) without formulas and now my search code works just fine.  Does VBA NOT find a date when that date is in a cell by formula or am I missing something?  Thanks again for your time.  Otto

I think you are missing something. It would be easier for me to tell if you just posted your code. Without seeing your code, here is my guess. The value in StartDate is a String value and the date in the cell is a real Excel date. Dates in Excel and VBA are not what you see; rather, they are floating point numbers that get displayed looking like a date for the convenience of the human user. The whole number part of the date's underlying value is an offset from "date zero" and the decimal part is the fraction of a 24-hour day represented by the time. So, ignoring any time considerations, today's date (February 17, 2011) is seen as 40591. So, if you stored something like "2/17/2011", you would be asking VB to find that string of characters in values that looked like 40591. So, Excel will never find 40591 if you tell it to look for "2/17/2011". Store a real date in StartDate and I think all will be well. For example, change the data type of StartDate to Date and then make this assignment...

StartDate = Date(2011, 2, 17)

then try your Find function.

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 3

Rick

Thanks for your reply.  rStartDate is a range and StartDate is a Date value.  I am searching a range of dates to find the cell that holds the date.  I have done some small tests and I have found that VBA will not find a date in a range if that date is in its cell by formula.  That was a surprise.  I went back to my original file and wrote some simple loop code to enter all the dates (about 400 consecutive dates) without formulas and now my search code works just fine.  Does VBA NOT find a date when that date is in a cell by formula or am I missing something?  Thanks again for your time.  Otto

Answer 4

You did not show us enough code. Do you have anything changing the StartDate value? If not, then that is the only date that will be found. I'll point out in case you it is a typo, but you are setting rStartDate (note the leading 'r' character), but searching for StartDate (no leading 'r'). If you post your code, we may be able to search down your problem for you, otherwise we can only take wild guesses.

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.