Hi Bob,
You've fixed my problem with just 2 little but important " " and a &
I'll try to remember this one.
Thank you so much.
Louise
Le --22062012 à 03:34, Bob Phillips a écrit :
> [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"). FormulaArray = "=CorrMatrix(RC[-14]:R[" & iLimitRow &
> "]C[-4])"
> End Sub
> [/Code]
>
>
>
>
>
> From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
> Of garilou
> Sent: 22 June 2012 06:09
> To: ExcelVBA@yahoogroups.com
> Subject: [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
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ----------------------------------
> 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
>
> ----------------------------------Yahoo! Groups Links
>
>
>
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
----------------------------------
Tidak ada komentar:
Posting Komentar