VBA code to find last entry on a certain date and bring back the text in the adjacent

[pedros23]

Hi,

I hope I'm making sense here.

I have a spreadsheet with hundreds of sheets on it (one for each of my customers)

Basically they each have an amount of money from which they can order goods from me. I need to be able to find out the balance of each fund on any given date in the past and wondered if there is a VBA code that create a summary sheet.

There could be several entries for the same date, but at the same time there may be no entries for that date, therefore I need it to search for either the last entry on the date requested or the next date before this with an entry.

I have attached an example speadsheet.

Many thanks in advance.

[alansidman]

Here is some code that will search for the specific date and return the information only if the date is found. Not sure how to tweak this to find the next earlier date, but perhaps someone else can tweak this for you.

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

[pedros23]

Hi Alan,

Thanks for the reply, however when I run the macro I get a Run-time error 9 - Subscript out of range.

[alansidman]

Which line of code is highlighted when you debug the code?

[alansidman]

Example Fund.xlsm

Unable to duplicate your issue. Here is the file that I tested on.

[pedros23]

Hi Alan,

Thanks for the reply, however when I run the macro I get a Run-time error 9 - Subscript out of range.

The following is highlighted

lr = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row

[alansidman]

Do you have a sheet named Summary in your workbook? If not, then replace "Summary" with the name of your summary sheet as described in your first post to this thread. In your first thread you indicated you wanted the results in a Summary Sheet, but you did not have one in your test file. I created one and called it Summary. Whatever yours is called needs to be put in this line of code.

[pedros23]

Do you have a sheet named Summary in your workbook? If not, then replace "Summary" with the name of your summary sheet as described in your first post to this thread. In your first thread you indicated you wanted the results in a Summary Sheet, but you did not have one in your test file. I created one and called it Summary. Whatever yours is called needs to be put in this line of code.

Thanks Alan,

I had a feeling I had missed something simple. You're help has been fantastic.

I just need to figure out how to make it find the next previous date and i will save me so much time.