VBA Find method fails on cells with 'custom' date format

[LeMans Logistics]

Hi all, my first post.....

I am having problems with using the Find method on a range which has cells formatted with a custom date format "dd-mmm". The underlying formula is a reference to another cell i.e =D264-1 and if I clear the format to general (40749) and with the custom format the correct date is shown '25-Jul'. If I select this cell and then switch to the VBA immediate panel,

? activecell.Formula
=D264-1
? activecell.Value
25/07/2011
? activecell.Value2
40749

Sample of code....

Dim rFound As Range
Dim LastCell As Range
Dim Search_Format As String
Dim Search_Number As Long

Search_Format = Format(Date, "dd-mmm")
Search_Number = DateDiff("d", #1/1/1900#, Date)
'Application.Goto reference:=Worksheets("Jim's hours").Range("A1")
Debug.Print ActiveCell.Address
With Worksheets("Jim's hours").UsedRange
'.Activate
'.Select
Set LastCell = .Cells(.Cells.Count)
Debug.Print LastCell.Address
Set rFound = .Find(what:=Search_Format, after:=LastCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not rFound Is Nothing Then
Application.Goto rFound.Offset(1, 0), False 'jump to todays date and down one row
If IsEmpty(rFound.Offset(1, 0)) Then
'Start time rounded down to the nearest quarter/hour
rFound.Offset(1, 0).Value = TimeSerial(Hour(Time), (Minute(Time) \ 15) * 15, 0)
'Finish time entered at 18:00 or 16:00 on a Friday etc.
Select Case Weekday(Date, vbUseSystemDayOfWeek)
Case 1 To 4
rFound.Offset(2, 0).Value = TimeSerial(18, 0, 0)
Case Else
rFound.Offset(2, 0).Value = TimeSerial(16, 0, 0)
End Select
'Debug.Print DateSerial(Year(Date), Month(Date), Day(Date)) + TimeSerial(Hour(Time), (Minute(Time) \ 15) * 15, 0)
Else
MsgBox "Today's date is: " & Date, , "Start & Finish time already entered into today's date !"
End If
Else
MsgBox "Today's date not Found" & _
vbCrLf & "You need to resolve this for the" & _
vbCrLf & "rest of the spreadsheet to work!" & _
vbCrLf & "See Bob & Tom!"
End If
End With
Set rFound = Nothing

If I format to "Short date" it seems to work but I'm unable to change the formatting in the original worksheet.

My question is...what should I search for? I have tried formatting date using Const kUsDate = "mm\/dd\/yyyy" as suggested in previous posts and also converting to Long Integer using Clng(..) but still no luck..

Any help much appreciated.
Many thanks, Jim

 

[GlennUK]

What happens if you use:

.Find(what:=Date

etc?

 

[LeMans Logistics]

Thanks for the reply Glenn,

Same result of 'rfound = nothing'.

I had originally use 'Date' but then found many posts about correct data formats and 'issues' with VBA and searching.

Many thanks, Jim.

 

[GlennUK]

Are you sure that the search area has real dates ( and not strings )?

 

[LeMans Logistics]

Hi Glenn,
Well no I'm not sure.

What does work is if I change the cell format to a standard 'date' picked form the Date format list and not the custom window, and change the xlFormulas to xlValues in the find method as:

Dim rFound As Range
Dim Search_Format As String

Search_Format = Format(Date, "dd/mm/yy")
With Worksheets("Jims hours (2)").UsedRange
.Activate
.Select
Set rFound = .Find(what:=Search_Format, after:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Not rFound Is Nothing Then
Application.Goto rFound, False
ActiveWindow.ScrollRow = rFound.Row - 20
MsgBox rFound.Row - 20
Else
MsgBox "Today's date not Found" & _
vbCrLf & "You need to resolve this for the" & _
vbCrLf & "rest of the spreadsheet to work!" & _
vbCrLf & "See Bob & Tom!"
End If
Set rFound = Nothing
End With
So it looks as though the problem is the formatting part.

Interesting also was the .NumberFormat ("d-mmm") and .NumberFormatLocal ("dd-mmm") on a cell whose custom format was "dd-mmm".........ah and it only fails at beginning of the month......

That's it! I knew it had worked, "10-Aug" works, "01-Aug" FAILS...

Now fixed....

Search_Format = Format(Date, "d-mmm") 'Must use .NumberLocal format

It also failed if I changed back xlValues back to xlFormulas so no hiding any rows-columns then!

Thanks for your input, Jim

 

[GlennUK]

My pleasure