[ExcelVBA] Cannot adapt matrix formula.

 

Hi everyone,
It has been a longtime since I asked your help, you have given me so many tools to work on my own.

But now I have this problem.
I have found a correlation matrix function that saves me lots of time.
http://www.financialmodelingguide.com/analytical-tools/benninga-correlation-matrix/
I paste the code in case someone would like to try it.
=========================================

Function CorrMatrix(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numCols As Integer
numCols = rng.Columns.Count
numRows = rng.Rows.Count
Dim matrix() As Double
ReDim matrix(numCols - 1, numCols - 1)

For i = 1 To numCols
For j = 1 To numCols
matrix(i - 1, j - 1) = _
Application.WorksheetFunction.Correl(rng.Columns(i), rng.Columns(j))
Next j
Next i
CorrMatrix = matrix
End Function

=========================================
If I enter it in a macro (that I have recorded), I get:
[Code]
Sub Test_Matrix()

Range("BA2:BK12").Select
'The range where the analysis output will come
Selection.FormulaArray = "=CorrMatrix(RC[-14]:R[18]C[-4])"
'RC[-14]:R[18]C[-4] = Data range to be analyzed.
End Sub
[/Code]
and this works perfectly.

But I must do the same every time I add a row to the matrix array (here it would be R[18]).
So I would like to be able to add rows to the range that will be analyzed.

But lets say I just want to replicate the same, but requesting the last row with an input box I would have:

[Code]
Sub Test_Matrix()
dim iLimitRow as Integer
iLimitRow = InputBox("Enter last row number")

' here in my test I enter 18 in the input box)
Range("BA2:BK12").Select
Selection.FormulaArray = "=CorrMatrix(RC[-14]:R[iLimitRow]C[-4])"
End Sub
[/Code]

I get the error message:
Run-time error '1004'
Unable to set the FormulaArray property of the Range class

I have Googled a lot for that error, but nothing seemed to apply.
(Excels Help still less)

I am sure my eyes do not see something obvious, but I guess your eyes will see it very fast: although I use it often, I am still not too confortable withe the R1C1 notation.

Thanks for any solution.

Louise

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (1)

Recent Activity:

• New Members 7

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___