Using Find on Calc. Cols.

  • Thread starter Thread starter J. Lund
  • Start date Start date
J

J. Lund

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
 
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)
 
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
 
Back
Top