Ctrl + F can't find value in date format; eomonth formula used

[ironny90]

Hi there! I have a column of dates calculated by =eomonth, so it starts with 1/31/2022 and goes on.

Now I am trying to find 3/31/2022, shown as Mar-22 and calculated by =eomonth(A2,1) . The value is 44651 if copy and pasted as value.

The problem is whatever I tried, I could not find it. I tried 03/31/2022,3/31/2022,44651,Mar-22, etc. And changed the search options to value as well.

Does anyone know what's the issue here? Thank you!

 

[ironny90]

I just ran the code. Here it says type mismatch? Why?

 

[jasonb75]

That was one of the errors that I suspected I might have made. I wasn't sure if it would work or not and didn't have the opportunity to test it before posting. I've corrected that one but I should mention that if the folder being used contains any files that are not workbooks then there will still be problems. I'll add an extra error trap into it later to allow for that, even if it might not be needed it might be best to err on the side of caution.

Option Explicit
Sub Macro1()
With Application
'    .ScreenUpdating = False
'    .Calculation = xlManual

        Dim fso As Object, oFolder As Object, wb As Object, wbk2 As Workbook
        Dim mVar As Variant, rFound As Range, sFound As Range
        Dim mArray As Variant
        Set fso = CreateObject("scripting.filesystemobject")
        Set oFolder = fso.getfolder("C:\Users\summary\test")
        For Each wb In oFolder.Files
            Workbooks.Open wb
            With wb
                mVar = .Sheets("Summary").Range("A1").Value
                With Workbooks("Test.xlsx").Sheets("test").Columns("A:A")
                    Set sFound = .Find(mVar, , xlFormulas, xlWhole, xlByRows, xlNext, False, False, False)
                End With
                If Not sFound Is Nothing Then mArray = .Offset(, 1).Resize(, 3).Value Else Exit Sub
            
                Set rFound = .Range("A10:A100").Find(Format(Date - 10, "mmm-yy"), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
            
                If Not rFound Is Nothing Then
                    With rFound
                        .Offset(0, 6).Value = mArray(1, 1)
                        .Offset(0, 11).Value = mArray(1, 2)
                        .Offset(0, 16).Value = mArray(1, 3)
                    End With
                End If
                .Save
                .Close
            End With
        Next
    .Calculation = xlAutomatic
    .ScreenUpdating = True
End With

End Sub

 

[ironny90]

Thank you so much! It worked!!!