using arrays to complete calcs

[Matt S]

All,

I am trying to use a column of my 2-dimensional array in a worksheet
function. I unfortunately do not know how to do this. This is what I have
so far below (cut from certain portions). I am trying to use the
correllation function to compare the column data of arrTop to another column
data of arrTop. I'm thinking of making the columns I'm comparing ranges
first, then putting them in the correl function?

Thanks for any help,
Matt


ReDim arrTop(1 To LastRow, 1 To 11)
arrTop = ActiveCell.Offset(-ActiveCell.Row + 1, -12).Range("B" &
StartingRow & ":L" & EndingRow).Value

For j = 1 To 10
For k = 2 To 10
ActiveCell.Offset(j, k).Value =
Application.WorksheetFunction.Correl(arrTop(, j), arrTop(, k))
Next k
Next j

 

[Jim Cone]

Dana DeLouis's response to your "simplify" post on 04/17/2009 answers this question too.
--
Jim Cone
Portland, Oregon USA




Matt S"
<[email protected]>
wrote in message
All,
I am trying to use a column of my 2-dimensional array in a worksheet
function. I unfortunately do not know how to do this. This is what I have
so far below (cut from certain portions). I am trying to use the
correllation function to compare the column data of arrTop to another column
data of arrTop. I'm thinking of making the columns I'm comparing ranges
first, then putting them in the correl function?
Thanks for any help,
Matt

ReDim arrTop(1 To LastRow, 1 To 11)
arrTop = ActiveCell.Offset(-ActiveCell.Row + 1, -12).Range("B" &
StartingRow & ":L" & EndingRow).Value

For j = 1 To 10
For k = 2 To 10
ActiveCell.Offset(j, k).Value =
Application.WorksheetFunction.Correl(arrTop(, j), arrTop(, k))
Next k
Next j

 

[Matt S]

So Jim,

I tried to apply what Dana used below. Is it correct?

Thanks,
Matt

For j = 1 To 9
For k = 1 To 9
With Application.WorksheetFunction
ActiveCell.Offset(j + 1, k).Value =
..Correl(.Index(arrTop, 0, k), .Index(arrTop, 0, j))
End With
Next k
Next j

It doesn't seem to give the same values if I were to do it by hand.

Thanks,
Matt

 

[Matt S]

ah nevermind... I am getting the same thing! GREAT THANK YOU!

 

[Dominik Petri]

Hi Matt,

try this:

with Application.WorksheetFunction
ActiveCell.Offset(j, k).Value = .Correl(.Index(arrTop, 0, j),
..Index(arrTop, 0, k))
End With

Not tested tough...

Hope this helps,
Dominik.