find works very oddly

[chfa]

Hi folks,

following code works somtimes, sometimes it doesn't:

Dim foundRange As Range, dateRange As Range
Dim cellAddr As String, dateVal As Date, workingDay As String

workingDay = "02.05.07"
Set dateRange = ThisWorkbook.Worksheets("Mai").Range("D536")
With dateRange
Set foundRange = .Find(what:=format(workingDay, "d") , _
LookIn:=xlValues, _
searchorder:=xlColumns,
_
SearchDirection:=xlNext,
_
LookAt:=xlPart)
Do
If foundRange Is Nothing Then Exit Do ' emergency exit
dateVal = foundRange.Value
Set foundRange = .FindNext(foundRange)
Loop Until dateVal = workingDay ' go 4it until found
End With ' search within a range

Any ideas ??

Thanx for help

chfa

 

[Bernie Deitrick]

Try this:

Sub TryNow()

Dim foundRange As Range
Dim dateRange As Range
Dim cellAddr As String
Dim dateVal As Date
Dim workingDay As String

workingDay = "05/02/07"

Set dateRange = ThisWorkbook.Worksheets("Mai").Range("D536")
Set foundRange = dateRange.Find(what:=Format(DateValue(workingDay), _
dateRange.Cells(1).NumberFormat), _
LookIn:=xlValues, _
searchorder:=xlColumns, _
SearchDirection:=xlNext, _
LookAt:=xlPart)
If foundRange Is Nothing Then
MsgBox "Not Found"
Else
MsgBox "That date is in " & foundRange.Address
End If

End Sub


Note that workingDay = "02.05.07" did not work for me - The decimal separator isn't valid in VBA,
IIRC, and dates are by default US -centric in VBA - mm/dd/yy....

HTH,
Bernie
MS Excel MVP

 

[Carl Hartness]

Problems I have encountered with Find Method:
1. Cells not all formatted with the same format.
2. Saved values hanging around from a different Find. Per the Find
Method Help, "The settings for LookIn, LookAt, SearchOrder, and
MatchByte are saved each time you use this method. If you don't
specify values for these arguments the next time you call the method,
the saved values are used."
3. Not all the cells to be searched included in the range being
searched.

Carl.