SUMIF In macro code

[coop123]

Hello

I have a sumifs formula as part of a macro code.

ActiveCell.Formula = _
"=SUMIFS(N$12:N$" & ListEnd & ", $A$12:$A$" & ListEnd & ",""Non EU"",$B$12:$B$" & ListEnd & ",""="" & M706)"

This works perfectly, but cell M706 could be higher or lower depending on the data within the report each time it is run, hence using ListEnd.

I tried replacing & M706 with & RC[-1] when running it stops with error Application-Defined or Object-Defined error.

Can someone point out where I have made the mistake or how I can change the formula so it works.

Thanks in advance.

coop123

 

[mart37]

I think you need the function OFFSET.

 

[Fluff]

Should M706 be col M row ListEnd?

 

[coop123]

I am trying to sum values in column N which match criterial "Non EU" from column A and match value in cell adjacent to formula cell from column B.

Starting position will always be column N but could be any row.

 

[Fluff]

What determines which row in col M the formula should look at? is it the ListEnd row?

 

[coop123]

See code below that positions cell formula is entered in. Cell looked at for Criteria 2 is 1 cell to the left.

Range("M" & ListEnd + 10).Select
ActiveCell.Offset(0, 1).Select

 

[Fluff]

Ok, how about

With Range("N" & ListEnd + 10)
   .Formula = _
      "=SUMIFS(N$12:N$" & ListEnd & ", $A$12:$A$" & ListEnd & ",""Non EU"",$B$12:$B$" & ListEnd & ",M" & .Row & ")"
End With

 

[coop123]

Thank you Fluff, that piece of code works brilliantly, it has solved the problem I have been having.

Thanks again

Coop123

 

[Fluff]

You're welcome & thanks for the feedback.