Fill in formula with dymanic address

[Lok Tak Cheong]

In worksheet1, I want to add formula such as
=SUMIF(worksheet2!$E$2:AA$241,$A2,worksheets2!AA$2:AA$241)

However, the range (the last row) is changed accordingl to the import file.

I attempt to use VBA to fill in the formula.

 

[Guest]

You may want to look at creating a dynamic range. Try putting something
like this in for the first range

=OFFSET('Worksheet2'!$E1,1,0,counta('Worksheet2'!$E:$E)-1,23)

And the second range
=OFFSET('Worksheet2!$AA1,1,0,Counta('Worksheet2'!$E:$E)-1,1)

I'm counting the # of cells in E to be consistent between the ranges. You
can then change your formula to

=SUMIF(FIRSTNAMEDRANGE,$A2,SECONDNAMEDRANGE)