find a date created by a formula

[gtd526]

Hello,
I'm using the following code but it can't find the date because its created by a formula (EOMONTH). If the date is entered manually, its selected.
How can we make it find a date created by a formula?

Set foundRng = Range("A:A").Find(DateSerial(Year(Date), Month(Date) + 1, 0))  'last day of current month
'find expenses
    If Not (foundRng Is Nothing) Then
        foundRng.Select
        ActiveCell.Offset(0, 21).Select
        Selection.Copy
        ActiveCell.PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Else
    End If

Thank you

 

[MARK858]

Try...

Set foundRng = Range("A:A").Find(DateSerial(Year(Date), Month(Date) + 1, 0), , xlValues, xlWhole, , xlNext, False)

 

[gtd526]

Try...

Set foundRng = Range("A:A").Find(DateSerial(Year(Date), Month(Date) + 1, 0), , xlValues, xlWhole, , xlNext, False)

Perfect. Thank you.

 

[MARK858]

You're welcome

Just an observation,
When you do a "Find" the parameters when you run the code are set by what your "Find" settings are in the Find window that appears when you do a Find manually.

About half the parameters retain a memory from when you last done a "Find" either manually or by code and so there is no default setting for all parameters.

Because of the above it very rare that you don't need to specifically define the parameters that are needed based on the specific data you are looking for and the layout when you start a new "find" (in your case the "LookIn" parameter being set as xlValues is essential), and so any code that only has the "What" parameter set is asking for issues to arise IMHO.

Range.Find method (Excel)

Office VBA reference topic

learn.microsoft.com