Glad it helped.
Here's a little more that may be useful. I had hard-coded the formula for 1000 rows. If the number of rows might be variable and you want to have a formula that just addresses the relevant rows, you could use a structure like this. You should still recognise the basic SUMPRODUCT formula but it has "#" as a placeholder for the last row. So the code finds the last row (lr) then replaces # with that value in the formula. To see how that works you could remove the .Value = .Value line (or just comment it out) and run this new code.
Code:
Sub SumDateData_v2()
Dim lr As Long
Const frmla As String = "=SUMPRODUCT(--(A1:A#>=C1),--(A1:A#<=D1),B1:B#)"
lr = Range("A" & Rows.Count).End(xlUp).Row
With Range("E1")
.Formula = Replace(frmla, "#", lr, 1, -1, 1)
.Value = .Value
End With
End Sub
Peter,
Happy Easter. Your too good.
Okay, I used your formula in a single page but now I have been dreaming on how to incorporate into the following. The code below checks all pages that contains the Fvalue and extracts information from each page. Instead of having one workbook for each record If I combine all worksheets into one workbook I can save files but the code you wrote calcuates well for a single worksheet but if I try to add to the following code between the '******************* where it says get monthly miles it calcuates from the summary page and not the target worksheet. The code between the '^^^^^^^^^^^^^^^^^^ (get total miles) works excellent getting the total using Application.Max(MilesRange) across sheets. This is the model I would like to adapt your sumproduct code to. Any thoughts. Thanks again for your work.
Sub GenerateSummaryReports()
'
'unlock
ActiveSheet.Unprotect
Dim sh As Worksheet
Dim FValue As String
Dim SumSheet As String
'Clear old report
Range("B9:Y47").Select
Selection.ClearContents
'
'What is the name of summary sheet?
SumSheet = "Summary Report"
'Which row in Summary sheet are we starting on?
i = 9
'Which column in summary sheet?
x = "B"
'
FValue = Range("A6").Value
'
'
With Worksheets(SumSheet)
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> SumSheet Then
If ws.Range("P1") = FValue Then
.Cells(i, x) = ws.Range("C11").Value 'Plate
.Cells(i, x).Offset(0, 1) = ws.Range("R4").Value 'Vehicle Type
.Cells(i, x).Offset(0, 5) = ws.Range("R7").Value 'Year
.Cells(i, x).Offset(0, 7) = ws.Range("R3").Value 'Location
.Cells(i, x).Offset(0, 9) = ws.Range("R2").Value 'Assigned
.Cells(i, x).Offset(0, 11) = "Trips" 'ws.Range("D9").Value 'No. of Trips
'get monthly miles***********************************************************
I want to modify your code below to match the style in the section below it between the '^^^^^^^^^
With Range("D7")
.Formula = "=sumproduct(--(C15:C100>=C7),--(C15:C100<=C8),M15:M100)"
.Value = .Value
End With
.Cells(i, x).Offset(0, 12) = "Mon" 'ws.Range("J11").Value 'Miles - Month
'****************************************************************************
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
'get total miles on vehicle - THIS WORKS
'Create and fill variables
'variables
Dim MilesRange As Range
Dim MaxMiles As Double
lnglast = ws.Range("A" & Rows.Count).End(xlUp).Row
'calculation Ranges
Set MilesRange = ws.Range("L15:L" & lnglast)
'calculations
MaxMiles = Application.Max(MilesRange)
MaxMiles = Format(MaxMiles, "0.0")
.Cells(i, x).Offset(0, 13) = MaxMiles 'ws.Range("J11").Value 'Miles - Tot
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
.Cells(i, x).Offset(0, 15) = "GMon" 'ws.Range("J11").Value 'Gas - Month
.Cells(i, x).Offset(0, 17) = "serO" 'ws.Range("J11").Value 'Service - Oil
.Cells(i, x).Offset(0, 18) = "serC" 'ws.Range("J11").Value 'Service - Cleaned
.Cells(i, x).Offset(0, 20) = "A" 'ws.Range("J11").Value 'Safety Check A
.Cells(i, x).Offset(0, 21) = "B" 'ws.Range("J11").Value 'Safety Check B
.Cells(i, x).Offset(0, 22) = "C" 'ws.Range("J11").Value 'Safety Check C
i = i + 1
End If
End If
Next ws
End With
'
'
End Sub
