Do you mind posting your VBA code and sample workbook as well?

The trick is to get rid of the INDIRECT, as it is volatile. I realize one of your other threads was about whether INDIRECT was needed, but that thread did not have the context of what your end-goal is.

The way I see it, you seem to be writing this formula in a cell, then copying/filling down. If the cells do not shift around after that (meaning no cut/paste or insert/delete) then these values are more or less static, and so can be generated without INDIRECT some way. I believe the best approach, without having seen your workbook or knowing your end-goal, is to loop through the cells in question and write a less volatile formula into them, taking advantage of VBA's power and flexibility.