PDA

View Full Version : Solved: Finding date in a range



JimmyTheHand
10-20-2007, 02:14 AM
How is Find method used with dates?

Range("F1:IV4") contains dates. I want to find the cell that has a value of e.g. "10.01" (1st of October).
I tried to search in values, formulas, even in comments, and tried to change the format of the value that is being sought. I tried to utilize Find in every way I could think of, but it never finds the right cell. It never finds anything! :banghead:

See the attached sample workbook for reference.
Here is one version of the code I used. Can anyone make the right adjustments? Or tell me why is it impossible?

Sub FindDate()
Dim i As Long, Rng As Range, Hit As Range
Set Rng = Sheets("Jelen").Range("F1:IV4")
For i = 9 To 12
Set Hit = Rng.Find(Range("B" & i), , xlValues, xlPart)
If Hit Is Nothing Then
Range("C" & i) = "#error"
Else
Range("C" & i) = "Hit"
End If
Next
End Sub
:help

Thanks,

Jimmy

Bob Phillips
10-20-2007, 02:59 AM
I can get it to find the date formatted cells, but then it doesn't find the integre formatted cells.

This is most odd to me, I expected it to find them all as they are all underlying numbers, so should have no problem.

Nurofen
10-20-2007, 03:04 AM
Hi,

Try this I'm not sure if its what your looking for but all the errors turn to hits



Sub FindDate()


Dim i As Long, Rng As Range, Hit As Range


Set Rng = Sheets("Jelen").Range("F1:IV4")

For i = 9 To 14
Set Hit = Rng.Find(Range("B100" & i), , xlFormulas, xlPart)
If Hit Is Nothing Then
Range("C" & i) = "#error"
Else
Range("C" & i) = "Hit"
End If
Next
End Sub


Post Edit: Sorry no its not what your looking for my bad.
Nurofen:(

mdmackillop
10-20-2007, 03:15 AM
Hi Jimmy,
Having trouble applying this to your data, but have a look at the explanation.
http://www.ozgrid.com/VBA/find-dates.htm
Regards
MD

JimmyTheHand
10-20-2007, 05:06 AM
MD, Bob,
Thanks for the hints, both of you. I suspected that the problem lies with cellformats, but wouldn't have thought that it's the search range which must be formatted differently.
It turned out that my code works only if the search range numberformat is "m/d/yyyy". However, I want the dates to be displayed as shown in the sample workbook (attached previously). So I put in a format change just before and after the search.

Sub FindDate()
Dim i As Long, Rng As Range, Hit As Range

Set Rng = Sheets("Jelen").Range("F1:IV4")
Application.ScreenUpdating = False
Rng.NumberFormat = "m/d/yyyy"
For i = 9 To 12
Set Hit = Rng.Find(Range("G1").Value, , xlValues, xlPart)
If Hit Is Nothing Then
Range("C" & i) = "#error"
Else
Range("C" & i) = "Hit"
End If
Next
Rng.NumberFormat = "mm/dd"
Application.ScreenUpdating = True
End Sub

People, after receiving help, always say this is a great site, now I feel compelled to do the same :thumb Thanks agains, guys.

Jimmy