-
Please help - writing simple formula in VBA
Hi all, this is my first post. I appreciate any help in advance.
In the attached spreadsheet there is a data set that is highlighted in yellow. This is a covariance formula (correlation * standard dev asset1 * standard dev asset 2). I am trying to automate this in VBA so when i click the button at the top, the covariance matrix is filled in using that formula automatically.
I was trying to write something using offsets, but kept getting data type mismatch errors.
I am hoping someone might be able to point me in the right direction as to how I might go about doing this!
Many thanks!!!!
Pete
-
Welcome to the forum!
It is unclear what is what. The covariance is filled where? Correlation, sd1 and sd2 are where?
-
Mr Hobs, thanks for responding
The correlation matrix is b30:k39
The covariance matrix is b43:k52
The standard deviations are b27:k27
So for the covariance of large cap value to mid cap growth, the formula is correlation of mid cap growth to large cap value * standard dev of large cap value * standard dev of mid cap growth)
So what i am looking to do is make cells b43:k52 calculate that formula once the correlation matrix is populated (this is done by Private Sub correlmatrix1 in the vba code)
does that make sense? i'm trying to be as clear as i can...maybe...
Thanks!
-
Since the covariance matrix (B43:K52) is already using formulas the recalculations of all these cells will happen automatically if Workbook calculation is set to Automatic. You could also add
Application.CalculateFull
at the end of your macro to force full recalculation even if the user set the Workbook calculation to something other than Automatic.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules