Inserting countifs to the last row

[NocoffeeNoWorkee]

I would like to enter a countifs formula into the last row of Col A and have it be able to apply to any columns if it were copy&pasted.

Criteria: Count everything in between the dates in Cells A1 and A2.
Criteria Range: Everything from Cell A3 to the last Row or one above the countifs function.

I tried using code to enter the formula as a string:
[TABLE="class: outer_border, width: 750"]
<tbody>[TR]
[TD]
Sub Countifs4()
Worksheets("MVT_TREND").Range("A" & Rows.Count).End(xlUp).Offset(1).Formula = "=COUNTIFS(A3:Range(""A"" & Rows.Count.End(xlUp)),"">=""&A1,A3:Range(""A"" & Rows.Count.End(xlUp)),""<""&A2)"

End Sub
[/TD]
[/TR]
</tbody>[/TABLE]



but it gives a #NAME? error in the correct cell. I think it has something to do with the "A & last row" part.
Any ideas?

 

[Jonmo1]

Try

Worksheets("MVT_TREND").Range("A" & Rows.Count).End(xlUp).Offset(1).Formula = "=COUNTIFS(A3:" & Range("A" & Rows.Count).End(xlUp).Address(0, 0) & ","">=""&A1,A3:" & Range("A" & Rows.Count).End(xlUp).Address(0, 0) & ",""<""&A2)"

 

[NocoffeeNoWorkee]

Thats perfect. Thanks a lot.

 

[Jonmo1]

You're welcome.