Help in VBA

[Gaius Marius]

Hi there,

I am trying to have my VBA function to show the correlation between varying sets of variables. Now, from going to one serie to the next ain't my problem, but I need to also vary the lenght considered.




For k = 1 To 10

ReDim BaseArray(1 To (4 * k))
ReDim EvalArray(1 To (4 * k))


For j = 1 To (4 * k)

BaseArray(j) = ExcessBase.Cells(41 - j, 1)
EvalArray(j) = ExcessEvaluated.Cells(41 - j, 1)

Next j

If Application.WorksheetFunction.Count(EvalArray) = 4 * k Then
DestinationRange.Cells(i - 1, 12 - k) = Application.WorksheetFunction.Correl(BaseArray, EvalArray).Value
Else
DestinationRange.Cells(i - 1, 12 - k) = "N/A"
End If

Next k


K is the # of years evaluated. The data is per quarter, so there are 4 data points per years evaluated. ExcessBase and ExcessEvaluated are the universal data I am trying to use for the correlation calculation, set as Range.

Now, I am trying to set the lenght of the two XXXXArray variables (they are Long) for every K going from 1 year to 10 years. I think I did the reDim right on that.

My problem is at this specific line:

BaseArray(j) = ExcessBase.Cells(41 - j, 1)
EvalArray(j) = ExcessEvaluated.Cells(41 - j, 1)

It blocks. I am trying to set each values of the XXXXArray variables by starting at the latest (and most recent values), and then work my way up. However, I can't seem to attribute any value to my arrays based on the ranges!


The error message is "Invalid qualifier"

Can anyone help?

[factotum]

I think it would help to see how ExcessBase and ExcessEvaluated are declared and initialised, since they're in the lines of code that are failing--it's difficult to say what the problem is given the code supplied.

[Gaius Marius]

It's okay, I fixed it... (http://xkcd.com/979/)