Array Variables and Formulas

zdodson

Board Regular
Joined
Feb 29, 2012
Messages
124
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!

VBA Code:
'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
 
Last edited:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top