cortexnotion
Board Regular
- Joined
- Jan 22, 2020
- Messages
- 150
- Office Version
- 2013
- Platform
- Windows
Hi All
Can you help me with the sumproduct formula in my code - it is returning a VALUE entry in the cell atm.
Can you help me with the sumproduct formula in my code - it is returning a VALUE entry in the cell atm.
Code:
Sub check()
Application.ScreenUpdating = False
Dim wbtracker As Workbook
Dim WeekRng As Range, TypeRng As Range, TotRng As Range
Dim Chart1 As String, Chart2 As String
Dim WK1 As Date, WK2 As Date, WK3 As Date, WK4 As Date, WK5 As Date, WK6 As Date, LRhh As Long
Set wbtracker = Workbooks.Open("S:\temp\Tracker.xlsx")
LRhh = wbtracker.Sheets("HH").Range("A" & Rows.Count).End(xlUp).Row
Set WeekRng = wbtracker.Sheets("HH").Range("A2:A" & LRhh)
Set TypeRng = wbtracker.Sheets("HH").Range("B2:B" & LRhh)
Set TotRng = wbtracker.Sheets("HH").Range("W2:W" & LRhh)
Chart1 = "Raw"
Chart2 = "Good"
WK1 = Application.Evaluate("INT((TODAY()-1)/7)*7+1")
WK2 = Format(WK1 - 7, "dd/mm/yyyy")
WK3 = Format(WK2 - 7, "dd/mm/yyyy")
WK4 = Format(WK3 - 7, "dd/mm/yyyy")
WK5 = Format(WK4 - 7, "dd/mm/yyyy")
WK6 = Format(WK5 - 7, "dd/mm/yyyy")
With wbtracker.Sheets("Work")
.Range("A2").Value = Application.Evaluate("(SumProduct((WeekRng = WK1)) * (TypeRng = Chart1) * (TotRng))")
.Range("A3").Value = Application.Evaluate("(SumProduct((WeekRng = WK2)) * (TypeRng = Chart1) * (TotRng))")
.Range("A4").Value = Application.Evaluate("(SumProduct((WeekRng = WK3)) * (TypeRng = Chart1) * (TotRng))")
.Range("A5").Value = Application.Evaluate("(SumProduct((WeekRng = WK4)) * (TypeRng = Chart1) * (TotRng))")
.Range("A6").Value = Application.Evaluate("(SumProduct((WeekRng = WK5)) * (TypeRng = Chart1) * (TotRng))")
.Range("A7").Value = Application.Evaluate("(SumProduct((WeekRng = WK6)) * (TypeRng = Chart1) * (TotRng))")
End With
End Sub
Many thanks