R1C1 Code and Sum Product

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

R1C1 Code and Sum Product

Post by JoeExcelHelp »

Hope everyone is well,

This code I created runs through its cycle but at the end returns #VALUE! in all cells within the range AA2:LB2
I'm also attaching the formula it was produced from

Thank You

Code: Select all

=SUMPRODUCT(($A$2:$A$572<=AA$1+TIME(0,1,0))*($B$2:$B$572+($A$2:$A$572>$B$2:$B$572)>=AA$1+TIME(0,1,0)))+SUMPRODUCT(($M$2:$M$572>$N$2:$N$572)*($N$2:$N$572>=AA$1+TIME(0,1,0)))

Code: Select all

Sub HiringPlan_Surplus()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
        
        With Worksheets("Data").Range("AA2:LB2")
            .FormulaR1C1 = "=SUMPRODUCT((Data!R2C1:R600C1<=R1+TIME(0,1,0))*(Data!R2C2:R600C2+(R2C1:R600C1>R2C2:R600C2)>=R1+TIME(0,1,0)))" & _
                            "+SUMPRODUCT((Data!R2C13:R600C13>R2C14:R600C14)*(Data!R2C14:R600C14>=R1+TIME(0,1,0)))"
            .Value = .Value
        End With
        
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
 
End Sub

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: R1C1 Code and Sum Product

Post by HansV »

You use R1. This is the entire row 1 instead of AA$1 in your formula. Use

Code: Select all

            .FormulaR1C1 = "=SUMPRODUCT((Data!R2C1:R600C1<=RC1+TIME(0,1,0))*(Data!R2C2:R600C2+(R2C1:R600C1>R2C2:R600C2)>=RC1+TIME(0,1,0)))" & _
                            "+SUMPRODUCT((Data!R2C13:R600C13>R2C14:R600C14)*(Data!R2C14:R600C14>=RC1+TIME(0,1,0)))"
Best wishes,
Hans

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Re: R1C1 Code and Sum Product

Post by JoeExcelHelp »

Thank You Hans