1 Attachment(s)
$15 To Amend Macro To Find The Next Available Earlier Or Previous Date
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.
Code:
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.
