How to set variable range using a macro

[coop123]

Hello

I have a macro in which I need to add a SUMIF formula which has the criteria range as fixed so I can copy across range of columns. The column length is the same across the report, but each time the report is run the column length can vary.

To get the last row in a column I have used

Dim ListEnd As Integer

This works fine and gives the correct result. See below.

Range("M701").Formula = "=SUMIF(A12:A" & ListEnd & ",""EU"",M12:M" & ListEnd & ")"

My problem is I need A12:A to be fixed when I copy formula across other columns.

Any help would be much appreciated

Coop123

 

[Fluff]

How about

"=SUMIF($A12:$A" & ListEnd & ",""EU"",M12:M" & ListEnd & ")"

 

[coop123]

Hello Fluff

That works perfect.

I have been looking at complicated ways of trying to fix the range.

Should try to keep things simple.

Thanks again.

Coop123

 

[Fluff]

You're welcome & thanks for the feedback.