Inserting Array Formula to sum cell ranges with variable rows in excel vba

Question

I need to insert an array formula at numerous cells to first round and then sum cells in a range. However, the range is not always the same and hence, I'll have to pull in row numbers defining the range from variables. This doesn't seem to work:

Set ssheet = ThisWorkbook.Sheets("1 to 50")    
With ssheet.Range(Cells(1, 8), Cells(3200, 8))
Set x = .Find("Rate", LookIn:=xlValues, Lookat:=xlWhole)
tworow = x.Row
Set y = .Find("Total=", LookIn:=xlValues, Lookat:=xlWhole)
nextrow = y.Row
End With
l = tworow + 1
m = nextrow - 1
ssheet.Cells(nextrow, 9).FormulaArray = "=sum(round("I" & l & ":" & "I" &m,2))"
End sub

I can select the desired range by this:

myrange = "I" & l & ":" & "I" & m
Range(myrange).Select

But the code for sum formula gives out "Expected end of statement". Please help.

What I basically need is how to use variables for row numbers in the ArrayFormula code. — Swopnil
try replacing the " charatcers with ' for the bits within round: "=sum(round('I' & l & ':' & 'I' &m,2))" — maxhob17
@dani this is an array formula since it rounds values in each of the cells of the range and only after that, it sums them up. Equivalent to CSE in excel. — Swopnil
@maxhob17 I tried that, now it says "Unable to set FormulaArray Property of Range Class" — Swopnil

SJR SJR · Accepted Answer · 2016-11-04T10:37:49

You need to sort out what how your string is made up, i.e. what should literally appear. Try this

ssheet.Cells(nextrow, 9).FormulaArray = "=sum(round(I" & l & ":I" & m & ",2))"

Inserting Array Formula to sum cell ranges with variable rows in excel vba

1 Answers