Finding a Date in Forumla via VBA

[Foreverlearning]

Hi,

I am having difficulty trying to find a date using VBA.

I have a date range like so.

Cell C5 =01/01/2012
Cell D5 =C5+1
Cell E5 = D5+1
Cell F5 = E5+1

Formatted as dd (day date only)

Which gives me the dates ok

When try to use find function in vba it just doesn't find any dates
This is what I have

Dim iRes As Integer, dFind As Date, strMonth As String, iM As Long
Dim rngDate As Range

    iRes = MsgBox(Prompt:="Print range", Buttons:=vbYesNo)
    
    If iRes = vbNo Then
        Exit Sub
    ElseIf iRes = vbYes Then
        strMonth = Me.cboMonth.Text
        iM = Month(DateValue("01-" & strMonth & "-1900"))
        dFind = DateSerial(Me.txtYear.Value, iM, 1)
        Set rngDate = ActiveSheet.Range("C4:ND4").Find(what:=dFind, LookIn:=xlFormulas)
    End If

rngDate always end up with nothing

How do I go about the correct way?

Thanks

[Sa DQ]

Option Explicit
Sub gpeDateFind()
 Dim jJ As Byte, Format_ As String
 Dim rDat As Range, fRng As Range
 
 Randomize:             jJ = 9 + Rnd() * 240 \ 1
 Set rDat = Range([c5], [c5].End(xlToRight))
 Format_ = rDat.NumberFormat
 rDat.NumberFormat = "MM/DD/yyyy"
 Set fRng = rDat.Find(Format([c5].Value + jJ, "MM/dd/yyyy"), , xlValues, xlWhole)
 If fRng Is Nothing Then
    MsgBox "Nothing"
 Else
    MsgBox fRng.Address, , DateAdd("d", jJ, [c5].Value)
 End If
 rDat.NumberFormat = Format_
End Sub

[Foreverlearning]

Hi,

Sorry, but I am a liitle confused as to what really is happening here.

But I can see that you have temporary formatted the dates to do a find then refromatted to original

Can I ask what is the (Randomize: jJ = 9 + Rnd() * 240 \ 1) for?

That is a bit confusing

Thanks
Lionel

[Sa DQ]

[C5].value => #1/1/2012#

In [D2] Add formula =9+INT(240*RAND())

And {ENTER}

[Foreverlearning]

Thanks Sa DQ

You have given me ideas for an alternative which works well.

Appreciate your help