Selection.Find a formatted date

[Guest]

I need to find the first cell in a sorted ascending column of dates (Col A,
formatted as ddd dd/mm/yyyy) that is equal or greater than a user input date
(Formatted as dd/mm/yy or dd/mm/yyyy). I use the following command :

Range("A:A").Select
Selection.Find(What:=Format(user_input, "#"), After:=ActiveCell,
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If the field in col A is numeric i.e. 38930 for Aug.1,2006 the command is
successful. If it is formatted as Tue 01/08/2006 it is not successful. Is
there something I have to change in the arguments of .find ? Mind you, I am
not accustomed to object handling code (With .. End With and so on)

Thanks in advance

P.S. The Selection.Find command is successful for exact matches. I haven't
tried yet the "greater than" possibility...

 

[Guest]

DoctorG, this should show you what you need to know. Essentially, you can
use the Application.FindFormat.NumberFormat equal to the NumberFormat
property of the range you are looking at.

First use the "CreateSampleRange" routine to create 4 sample data sets.
Then use the "Testfinder" routine and vvary the "lngWhich" variable from 1 to
4 to see how it can lookup for each of the different format types.

Regards,
Bill


Public Sub Testfinder()
Dim strFormat As String
Dim strDate As String
Dim rngFirst As Range
Dim lngWhich As long

' pick an arbitrary date (d/m/yyyy)
strDate = "19/8/2005"


lngWhich = 1
lngWhich = 2
lngWhich = 3
lngWhich = 4



Select Case lngWhich
Case Is = 1: Range("B:B").Select
' sample dates, formatted as "[$-409]mmmm d, yyyy;@"
Case Is = 2: Range("D").Select
' sample dates, formatted as "[$-409]dddd, mmmm dd, yyyy"
Case Is = 3: Range("F:F").Select
' sample dates, formatted as "m/d/yyyy"
Case Is = 4: Range("h:h").Select
' sample dates, formatted as "d/m/yyyy"
End Select

Set rngFirst = Selection.Cells(1)

Do While (Len(rngFirst.Value) = 0)
Set rngFirst = rngFirst.Offset(1, 0)
Loop

strFormat = rngFirst.NumberFormat
Application.FindFormat.NumberFormat = strFormat

Selection.Find(What:=Format(strDate, strFormat), _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Sub



Sub CreateSampleRange()
Range("B8").Select
ActiveCell.FormulaR1C1 = "8/16/2005"
Range("B9").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("B9").Select
Selection.Copy
Range(Selection, Range("B19")).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("B8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Range("D8").Select
ActiveSheet.Paste

Range("F8").Select
ActiveSheet.Paste

Range("H8").Select
ActiveSheet.Paste

Range("B8:B19").NumberFormat = "[$-409]mmmm d, yyyy;@"
Range("D819").NumberFormat = "[$-409]dddd, mmmm dd, yyyy"
Range("F8:F19").NumberFormat = "m/d/yyyy"
Range("H8:H19").NumberFormat = "d/m/yyyy"

End Sub

 

[Guest]

Bill, I understood the principle and was able to find a match.

Nevertheless, I wasn't able to make it work using "ddd dd/mm/yy" maybe due
to the fact that my "ddd" is in Greek. I changed the format of the whole
range to "#" and with exactly the same code (both the search range and the
search value are in the same format, using parameters) it worked. I can't
understand it but I worked around it.

Thanks a lot

DoctorG, this should show you what you need to know. Essentially, you can
use the Application.FindFormat.NumberFormat equal to the NumberFormat
property of the range you are looking at.

First use the "CreateSampleRange" routine to create 4 sample data sets.
Then use the "Testfinder" routine and vvary the "lngWhich" variable from 1 to
4 to see how it can lookup for each of the different format types.

Regards,
Bill


Public Sub Testfinder()
Dim strFormat As String
Dim strDate As String
Dim rngFirst As Range
Dim lngWhich As long

' pick an arbitrary date (d/m/yyyy)
strDate = "19/8/2005"


lngWhich = 1
lngWhich = 2
lngWhich = 3
lngWhich = 4



Select Case lngWhich
Case Is = 1: Range("B:B").Select
' sample dates, formatted as "[$-409]mmmm d, yyyy;@"
Case Is = 2: Range("D").Select
' sample dates, formatted as "[$-409]dddd, mmmm dd, yyyy"
Case Is = 3: Range("F:F").Select
' sample dates, formatted as "m/d/yyyy"
Case Is = 4: Range("h:h").Select
' sample dates, formatted as "d/m/yyyy"
End Select

Set rngFirst = Selection.Cells(1)

Do While (Len(rngFirst.Value) = 0)
Set rngFirst = rngFirst.Offset(1, 0)
Loop

strFormat = rngFirst.NumberFormat
Application.FindFormat.NumberFormat = strFormat

Selection.Find(What:=Format(strDate, strFormat), _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Sub



Sub CreateSampleRange()
Range("B8").Select
ActiveCell.FormulaR1C1 = "8/16/2005"
Range("B9").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("B9").Select
Selection.Copy
Range(Selection, Range("B19")).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("B8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Range("D8").Select
ActiveSheet.Paste

Range("F8").Select
ActiveSheet.Paste

Range("H8").Select
ActiveSheet.Paste

Range("B8:B19").NumberFormat = "[$-409]mmmm d, yyyy;@"
Range("D819").NumberFormat = "[$-409]dddd, mmmm dd, yyyy"
Range("F8:F19").NumberFormat = "m/d/yyyy"
Range("H8:H19").NumberFormat = "d/m/yyyy"

End Sub

I need to find the first cell in a sorted ascending column of dates (Col A,
formatted as ddd dd/mm/yyyy) that is equal or greater than a user input date
(Formatted as dd/mm/yy or dd/mm/yyyy). I use the following command :

Range("A:A").Select
Selection.Find(What:=Format(user_input, "#"), After:=ActiveCell,
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If the field in col A is numeric i.e. 38930 for Aug.1,2006 the command is
successful. If it is formatted as Tue 01/08/2006 it is not successful. Is
there something I have to change in the arguments of .find ? Mind you, I am
not accustomed to object handling code (With .. End With and so on)

Thanks in advance

P.S. The Selection.Find command is successful for exact matches. I haven't
tried yet the "greater than" possibility...