$15 To Amend Macro To Find The Next Available Earlier Or Previous Date

[pedros23]

Hi,

I have a workbook with a few hundred tabs on it (one for each of our customers)

Basically each one is a for a pot of money by which they can order stock from us and can increase from time to time according to their agreement with us.

I need to be able to report what the balance of each customers fund is on any given date.

I have the following macro which searches for the date and returns the figures on the summary page.

Option Explicit

Sub Summary()
    Dim w As Worksheet
    Dim i As Long
    'Dim rng As Range
    Dim d As Date
    Dim lrng As Range
    Dim lr As Long
    d = InputBox("What Date to Search")
    'Dim wsFunc As WorksheetFunction
    'Set wsFunc = Application.WorksheetFunction
    

    For Each w In Worksheets
        If w.Name <> "Summary" Then
        'Set rng = w.Range("L6:L" & Range("L" & Rows.Count).End(xlUp).Row)
        Set lrng = w.Range("L:M")
        lr = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row
        
            For i = 6 To w.Range("L" & Rows.Count).End(xlUp).Row
                On Error Resume Next
                'If w.Range("L" & i) = wsFunc.VLookup(d, lrng, 2, False) Then
                If w.Range("L" & i) = d Then
                w.Range("L" & i).Resize(, 2).Copy
                Sheets("Summary").Range("B" & lr + 1).PasteSpecial Paste:=xlValues
                Sheets("Summary").Range("A" & lr + 1) = w.Name
                End If
            Next i
        End If
    Next w
    MsgBox ("Complete")

End Sub

The issue I have is that there will be customers with no orders on the specified date, therefore I need it to return the figure for the next available previous date.

Thanks in advance.