is there anyone who can lend me a hand with this? I am stuck.
I am sure there is an easier way of doing this, but I would like to struggle through the long way.
I get an object-defined error '1004' when the sub gets to the correl calc
I have a number of return streams on sheet(rawdata), the macro would calc correlation and create the table on the second tab. I was successful in setting up the 'grid' and would like to have it calculated column by column. i fixed series1 until all 12 coefficients are populated in rows C2 to C14 on the second tab...after that I will offset it another column and so fort...am i being clear?
I struggled with this for a few hours...and I keep hitting a wall...can someone please direct me in the right direction using the same logic I laid out. (i am sure it can be done using various other, simpler macros, but at this point I want to do it my way, unless the logic is flawed)
any help is appreciated
I had a look at the worksheet and code you posted.
I got the same error you got.
Is Correl a user defined function? If it is then as far as I know you can't use it with WorksheetFunction.
I'm also not quite sure what you want as a result.
Took me long enough to find it! arrrgggh...
You are comparing the correl version of apples and oranges. The line that contains the correl function has an error in addressing so the two ranges do not equal and correl chokes.
Series1 is B3, Series2 is C3, startasset is B1
So range1 for correl is B3:B33 and range2 is B4:B33 according to your formula. If you're trying to loop across the columns try:
Anyways, make the two ranges equal and it will work!Code:for x = 1 to 11 resultcell.Value = Application.WorksheetFunction.Correl(Sheets("rawdata").Range(series1, series1.End(xlDown)), Sheets("rawdata").Range(startasset.Offset(2, x), startasset.Offset(2, x).End(xlDown))) Set resultcell = resultcell.Offset(1, 0) Set startasset = startasset.Offset(0, 1) next
Last edited by rbrhodes; February 3rd, 2005 at 12:06. Reason: spell check!
thanks for the response...I just realized that in my code startasset is b1 however when calling it in the correl function I offset it by 3 rows and 0 columns...it should be offset by only 2 rows.
the next step after having the first column of correl done is too move across the grid...I guess I have to reset the location of series1 and do the same drill...perhaps a nested loop within this current loop.
I fixed the offset for startasset...but now in a 'set' command I also offset series1 by one column, how can i get back to the original loop and run the new series1 through all 12 columns on rawdata sheet...after that's done...it would offset series one by another column (it would start at d3)..run through it all 12 columns...offset it by another 1...and so on
any suggestions...I am not sure how I could nest another loop within the original one??
thanks a lot pangolin..that's one way to do it.
however, I need it in a matrix...hence the results will be doubled.
I do have another much simpler macro to do the job but I was wondering if it can be done by applying the logic I laid out in the following macro:
Code:Sub create_correl_matrix() Dim series1 As Range Dim series2 As Range Dim resultcell As Range Dim destcell As Range Dim startasset As Range Set series1 = Sheets("rawdata").Range("b3") Set startasset = Sheets("rawdata").Range("b1") Set destcell = Sheets("correl_matrix").Range("b2") Set resultcell = Sheets("correl_matrix").Range("c2") Sheets("correl_matrix").Cells.ClearContents startasset.Resize(1, 12).Copy destcell.PasteSpecial xlPasteValues, , , True destcell.Offset(-1, 1).PasteSpecial xlPasteValues Do Until startasset = Empty resultcell.Value = Application.WorksheetFunction.Correl(Sheets("rawdata").Range(series1, series1.End(xlDown)), _ Sheets("rawdata").Range(startasset.Offset(2, 0), startasset.Offset(2, 0).End(xlDown))) Set resultcell = resultcell.Offset(1, 0) Set startasset = startasset.Offset(0, 1) Loop Set series1 = series1.Offset(0, 1) ' after series1 is offset I would like to get back to the loop and run it through all the columns again...once it finished series1 would be offset again...and so on End Sub
can this macro be just modified slightly? don't want to come through as bullheaded but I am trying to learn different ways of applying code...your help is appreciated.
I havent understood
I think the results in the file I attached are generated by way of a matrix (It is in the same format as used by us in research reports while presenting correlation factors over multiple asset classes??? )
...did you really click on the button labelled "click me" on the sheet correl matrix
There are currently 1 users browsing this thread. (0 members and 1 guests)