Using Find on Calc. Cols.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J. Lund - 25 Jul 2007 01:31 GMT

I am trying to search a column of date that contains dates that were
calculated. (starting date + # of days out = end date). I am using the
following search macro code. This code works when the date is entered into
the column. It does not work when the date is calculated with the above
formula.

Any ideas why this doesn't work for a calculated field?

Sub Find_Date()

Dim dteVariable As Date

dteVariable = #5/1/2007#

FindDate ActiveSheet.Range("u1:u100"), dteVariable

End Sub

Public Sub FindDate(r As Range, target As Variant)

Dim r1 As Range

Set r1 = r.Find(target)

If r1 Is Nothing Then

MsgBox target & " was not found."

Else

r1.Select

End If

End Sub

Reply to this Message

Dave Peterson - 25 Jul 2007 01:38 GMT

First, I'd add all those other arguments to the .find statement. .Find will use
whatever parms were used in the previous .find (either by the user or by code)
and that may not be what you want.

Second, .Find can be tempermental with dates.

Sometimes, a small change is enough:

Set r1 = r.Find(what:=clng(target), ....rest of parms here)

> I am trying to search a column of date that contains dates that were
> calculated. (starting date + # of days out = end date). I am using the
[quoted text clipped - 31 lines]
>
> End Sub

Signature

Dave Peterson

Reply to this Message

OssieMac - 25 Jul 2007 04:52 GMT

Like Dave says you need the other arguments. You can use xlFormulas or
xlValues when finding dates which are not the result of a formula on the
spreadsheet.

However if the dates are the result of a formula on the spreadsheet, then
use xlValues. The following will work for data where the dates are the result
of either a formula or directly entered.

Set r1 = r.Find(What:=target, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Regards,

OssieMac

> First, I'd add all those other arguments to the .find statement. .Find will use
> whatever parms were used in the previous .find (either by the user or by code)
[quoted text clipped - 41 lines]
> >
> > End Sub

Reply to this Message

Rate this thread: