A macro for portfolio variance

[pallavmishra]

Hi,

I am trying to automate a variance-covariance matrix to calculate portfolio variance for each day for 510 days. My macro, however, is overwriting the output for each cell. Please help me with the problem. Is there a way I could attach the Excel sheet?

Sub PortVar()
Dim i As Integer
For i = 1 To 510
Range("AA6").Value = Cells(3 + i, 10).Value
Range("AB7").Value = Cells(3 + i, 11).Value
Range("AC8").Value = Cells(3 + i, 12).Value
Range("AD9").Value = Cells(3 + i, 13).Value
Range("AB6").Value = Cells(3 + i, 14).Value
Range("AA7").Value = Range("AB6").Value
Range("AC6").Value = Cells(3 + i, 15).Value
Range("AA8").Value = Range("AC6").Value
Range("AD6").Value = Cells(3 + i, 16).Value
Range("AA9").Value = Range("AD6").Value
Range("AC7").Value = Cells(3 + i, 17).Value
Range("AB8").Value = Range("AC7").Value
Range("AD7").Value = Cells(3 + i, 18).Value
Range("AB9").Value = Range("AD7").Value
Range("AD8").Value = Cells(3 + i, 19).Value
Range("AC9").Value = Range("AD8").Value
Cells(3 + i, 21).Select
Selection.FormulaArray = "=MMULT(MMULT(R5C27:R5C30,R6C27:R9C30),R6C26:R9C26)"
Next i
End Sub



Thanks

Pallav

Excel version: 2013 Pro. Macro name: PortVar