On Sun, 3 Aug 2008 18:40:59 +0000 (UTC), Terry Detrie
wrote:


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?


Sub PredCurve()
Dim KSRng(5) As Range, ColorantVals(5) As Single
Dim vSuper(5) As Variant
Dim CurrSht As Worksheet, DestRng As Range
Dim i As Long, j As Long

Set CurrSht = Worksheets("KS-Data")

For X = 1 To 5
Rw = X + 10 'added for testing
vSuper(X) = CurrSht.Cells(Rw, 6).Resize(, 226).Value
ColorantVals(X) = 1 'added for testing
Next X

For i = 1 To 226
CalcCurve(1, i) = 0
For j = 1 To 5
CalcCurve(1, i) = CalcCurve(1, i) _
+ vSuper(j)(1, i) * ColorantVals(j)
Next j
Next i

Set DestRng = CurrSht.Cells(2, 6).Resize(, 226)
DestRng.Value = CalcCurve

End Sub
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com