Search code examples
excelvbavolatileexcel-indirect

Expected: end of statement error -- Coding a Volatile Formula

I have two volatile formulas that work fine when it is hard coded in Excel.. but this formula will be dynamic so I am inserting it in VBA and will loop later on.

    Worksheets("Interest Calculator").Range("E5").Formula = "=IF($C5>Inputs!E8-1,"",IF($C5<=Portfolio_Duration,(SUM($E$4:E4)+$G4)/Portfolio_Duration,(SUM(INDIRECT("E"&$C5-Portfolio_Duration+4):INDIRECT("E"&$C5+3))+$G4)/Portfolio_Duration))"

I am getting a compile error: Expected: end of statement and the "E" in INDIRECT("E"&$C5+3))+$G4)/Portfolio_Duration))" is highlighted.

How can I fix this code? Thanks!

If it helps, Error Duration = 55 AND Portfolio Duration = 36

Solution

  • Double all the "

       Worksheets("Interest Calculator").Range("E5").Formula = "=IF($C5>Inputs!E8-1,"""",IF($C5<=Portfolio_Duration,(SUM($E$4:E4)+$G4)/Portfolio_Duration,(SUM(INDIRECT(""E""&$C5-Portfolio_Duration+4):INDIRECT(""E""&$C5+3))+$G4)/Portfolio_Duration))"