I have an application where the user selects up to 5 data sets (each containing
exactly 226 values) and Excel calculates a weighted average. I use VLOOKUP
to get all 5 datasets in consecutive rows and then use SUMPRODUCT 226 times
(keying off another range containing the weight%). Functionally this approach
works just fine, but the calculation times are starting to become significant.
I have turned Automatic calculation off, but this requires me to set up triggers
off certain events to make sure I don't miss some key calculations.

I've decided to try to do all of the curve selection and calculation in a
UserForm. In the code below, ColorantVals() are the weight% of each colorant,
and the five datasets are populated in a Range array KSRng(). I copy the
values into 5 seperate Variant arrays and do my SUMPRODUCT using a For/Next
loop. The code below yields the desired result, but I have to wonder: Is
there a way to combine the 5 variant arrays into one super vArr() array?

Terry


Option Base 1
Option Explicit
Dim Rw As Integer, X As Integer
Public CalcCurve(1, 226) As Variant

Sub PredCurve()
Dim KSRng(5) As Range, ColorantVals(5) As Single
Dim vArr1 As Variant, vArr2 As Variant, vArr3 As Variant, vArr4 As Variant,
vArr5 As Variant
Dim CurrSht As Worksheet, DestRng as Range

Set CurrSht = Worksheets("KS-Data")
For X = 1 To 5
[snipped code that determines what Row dataset is in]
Set KSRng(X) = CurrSht.Range(Cells(Rw, 6), Cells(Rw, 231))
Next X

vArr1 = KSRng(1).Value
vArr2 = KSRng(2).Value
vArr3 = KSRng(3).Value
vArr4 = KSRng(4).Value
vArr5 = KSRng(5).Value

For X = 1 To 226
CalcCurve(1, X) = vArr1(1, X) * ColorantVals(1) + vArr2(1, X) * ColorantVals(2)
+ vArr3(1, X) * ColorantVals(3) + vArr4(1, X) * ColorantVals(4) + vArr5(1,
X) * ColorantVals(5)
Next X
Set DestRng = Range(Cells(2, 6), Cells(2, 231))
DestRng.Value = CalcCurve
End Sub