VBA Excel formulaarrey insert range into formula-CodePudding

I have code which looks like this.

wsModel.Range("C4").FormulaArray = "=MMULT(B11:L11,TRANSPOSE(B8:L8))"

But source data will not always be the same and length of row may also vary. (For example instead of L11 and L8 I may have AD11 and AD8 )

So I tried to wrote code, but this does not work

dataLastColumn = wsClose.Cells(1, Columns.Count).End(xlToLeft).Column
Set weightsRange = wsModel.Range("B11", wsModel.Cells(11, (dataLastColumn - 1)))
Set returnRange = wsModel.Range("B8", wsModel.Cells(8, (dataLastColumn - 1)))
wsModel.Range("C4").FormulaArray = "=MMULT(" & weightsRange & ",TRANSPOSE(" & returnRange & "))"

CodePudding user response：

Try this for your last line (untested) …

wsModel.Range("C4").FormulaArray = "=MMULT(" & weightsRange.Address & ",TRANSPOSE(" & returnRange.Address & "))"