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]

Sounds good! I will start another thread.

Let me try the code. Thank you!!!

 

[ironny90]

Oh my goodness it worked! Thank you thank you!!!

 

[ironny90]

I just learned how to use find function myself Now I can see how your code works. Thank you again!!!

 

[ironny90]

Hi Jason, hope you will see it - if I want to replace the date in eomonth formula to make the calculation 03/31/2022 instead of 04/30/2022, how should I do that?

I am still working on March files but the date has changed to April ^^ Thank you!

 

[jasonb75]

if I want to replace the date in eomonth formula to make the calculation 03/31/2022 instead of 04/30/2022, how should I do that?

For previous month you would need to change , 0 to , -1 in the EoMonth part (as shown below).

Dim rFound As Range
Set rFound = Range("A:B").Find(Format(Application.WorksheetFunction.EoMonth(Date, -1), "mmm-yy"), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
If Not rFound Is Nothing Then rFound.EntireRow.Select

If you need to do that for a set number of days each month then I should be able to set the code to adjust it automatically, just need to know what to set it to.

 

[ironny90]

For previous month you would need to change , 0 to , -1 in the EoMonth part (as shown below).

Dim rFound As Range
Set rFound = Range("A:B").Find(Format(Application.WorksheetFunction.EoMonth(Date, -1), "mmm-yy"), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
If Not rFound Is Nothing Then rFound.EntireRow.Select

If you need to do that for a set number of days each month then I should be able to set the code to adjust it automatically, just need to know what to set it to.

Hi Jason, thank you so much! So it's before the 10th of each month, I will be working on the files of previous month. After 10th I will be working on the current month.

I can manually adjust as well. But if you know how to adjust I can learn from you I just started learning vba this week so any new tricks are super exciting.

 

[ironny90]

Sub Macro1()
Set fso = CreateObject("scripting.filesystemobject")
Set ff = fso.getfolder("C:\Users\summary\test")
For Each file In ff.Files
Workbooks.Open file
Set wbk2 = ActiveWorkbook
Sheets("Summary").Select
Range("A1").Select
rngY = Range("A1").Value
Workbooks("Test.xlsx").Activate
Sheets("test").Select
Columns("A:A").Select
Selection.Find(What:=rngY, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
      
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)).Select

Selection.Copy

wbk2.Activate
Range("X3").PasteSpecial Paste:=xlPasteValues

Range("X3").Select
Selection.Copy

Dim rFound As Range
Set rFound = Range("A10:A100").Find(Format("44651", "mmm-yy"), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
If Not rFound Is Nothing Then rFound.Select

ActiveCell.Offset(0, 6).PasteSpecial Paste:=xlPasteValues

Range("Y3").Select
Selection.Copy

Set rFound = Range("A10:A100").Find(Format("44651", "mmm-yy"), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
If Not rFound Is Nothing Then rFound.Select

ActiveCell.Offset(0, 11).PasteSpecial Paste:=xlPasteValues

Range("Z3").Select
Selection.Copy

Set rFound = Range("A10:A100").Find(Format("44651", "mmm-yy"), , xlValues, xlPart, xlByRows, xlNext, False, False, False)
If Not rFound Is Nothing Then rFound.Select

ActiveCell.Offset(0, 16).PasteSpecial Paste:=xlPasteValues

Range("X3:Z3").ClearContents

Range("A1").Select

ActiveWorkbook.Save
ActiveWorkbook.Close

Next

End Sub

I also developed these codes yesterday with your help. I couldn't figure out the -1 part, but I replaced 03/31/2022 with 44651 and it worked. I know the codes might look very redundant ( you see your code has been used three times), but it works Any feedback will be much appreciated as well. Thank you thank you.

 

[jasonb75]

I've done a rewrite of your code but need to check it over before posting it to be sure that I've followed what yours is doing correctly. I'll try and get it done tomorrow (Sunday) if I get chance but more than likely it will not be until the start of the week.

I'll include some notes as feedback on your attempt (very good for a beginner) at the same time.

 

[jasonb75]

I haven't done any notes yet, Ill do those after correcting any errors that I've made in this, give this a quick test and see if it works as expected.

If it works, removing the apostrophes from the start of the 2 lines at the top should speed it up a bit.

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

        Dim fso As Object, oFolder As Object, wb As Workbook, 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]

I've done a rewrite of your code but need to check it over before posting it to be sure that I've followed what yours is doing correctly. I'll try and get it done tomorrow (Sunday) if I get chance but more than likely it will not be until the start of the week.

I'll include some notes as feedback on your attempt (very good for a beginner) at the same time.

Thank you so much for guiding the beginners on this forum. Much appreciated.