Covariance Matrix in excel

Hi! I am trying to complete a portfolio analysis in excel. We are doing this in a finance class and I am using VBA to make it easier and faster with calculations. I am left with a covariance matrix which needs to vary by size. I have tried to start where I calculate the first stock with the rest(vertical). But I am not sure why the code won`t work:

Please Login or Register  to view this content.

Expl:
wb.hs.Cells(i + 1, 2) starts with row 2 and column 2(will turn into variable later).
wb.ws.Range([Cells(1, 1), Cells(h, 1)]) is first stock
wb.ws.Range([Cells(1, i), Cells(h, i)]) is first stock and increases by 1 stock range each time i increases.

I appreciate any help. Thank you.

A Sheets collection is always associated with a particular workbook. If none is specified, the ActiveWorkbook.Sheets collection is assumed, so ws and hs were improperly defined. Note that the parent workbook is part of the worksheet object variable definition and need not be specified later. Just use ws and hs, not wb.hs.

Please Login or Register  to view this content.

If you wish to address sheets with the same name in different workbooks, define a string variable and use it as so:

Please Login or Register  to view this content.

Originally Posted by olli.excel

run time error 1004 = "Application-defined or object-defined error" on the line:

Please Login or Register  to view this content.

Remove the wb. at the beginning. You're specifying workbook TWICE, it's already part of the hs definition.
Also, I just realized the "internal" Cells references all apply to the ActiveSheet! You must specify ws for EACH ONE.

Try this:

Please Login or Register  to view this content.

Or better yet:

Please Login or Register  to view this content.

BTW, forum rules ask for CODE tags around your code (so it looks like the code above).
In Editing Mode, just select your code text and click the # button on the editing toolbar above the body of your post.

You're welcome, happy to help! - Lee