Array Variables and Formulas

[zdodson]

I am trying to create an array formula that will compute total planned hours against attendance levels to see whether a site's planned hours would be able to cover operational demand. When I go to run the code, I am getting a run-time error '13 Type Mismatch error, likely due to something with the variable declarations and (futile) attempts at creating the last array formula. Any help would be greatly appreciated! Thanks in advance!

'WORKSHEET VARIABLES
Dim p As Worksheet, u As Worksheet, x As Worksheet
Dim y As Worksheet, z As Worksheet

Set p = Worksheets("PROGRAM")
Set u = Worksheets("SUNDAY")
Set x = Worksheets("SHIFT OPERATING PLAN")
Set y = Worksheets("LABOR ASSUMPTIONS")
Set z = Worksheets("LP VS SITE DELTA")

'CRITERIA RANGES FOR SUMIFS CALCULATIONS
Dim sumRange As Range, actRange As Range, actCrit As Range, CritRange As Range

Set sumRange = u.Range("BG11:BG310")
Set actRange = u.Range("E11:E310")
Set actCrit = p.Range("AB8")
Set CritRange = u.Range("B11:B310")

'CRITERIA VARIABLES FOR SUMIFS
Dim Site1 As String, Site2 As String, Site3 As String
Dim Site4 As String, Site5 As String, Site6 As String
Dim Site7 As String, Site8 As String, Site9 As String
Dim Site10 As String

Site1 = x.Range("A9").Value
Site2 = x.Range("A81").Value
Site3 = x.Range("A153").Value
Site4 = x.Range("A225").Value
Site5 = x.Range("A297").Value
Site6 = x.Range("A369").Value
Site7 = x.Range("A441").Value
Site8 = x.Range("A513").Value
Site9 = x.Range("A585").Value
Site10 = x.Range("A657").Value

Dim Assumptions As Long

'CREATING CONSOLIDATED FORMULA
Dim Site1_Hours As Double
Dim Site1_RWA As Variant

Site1_Hours = WorksheetFunction.SUMIFS(sumRange, CritRange, Site1, actRange, actCrit)
Site1_RWA = WorksheetFunction.Index(Assumptions, _
            WorksheetFunction.Match(Site1 & "Attendance (%)", y.Range("C9:C314") & y.Range("F9:C314"), 0), 1)

z.Range("D27").FormulaArray = Site1_Hours * Site1_RWA

 

[jasonb75]

I'm not able to test your code at the moment but if I remember correctly, you can not concatenate ranges in vba: y.Range("C9:C314") & y.Range("F9:C314") in your code.

Also, the variable 'Assumptions' appears to have been declared but not defined. It is not clear what this variable should refer to.

With something like this, it would be easier for us to follow if you included a small (fictional) example of your sheet so that we can compare the code to what it is meant to be working with.