Using Sumifs in VBA where range changes

[cwilliams96]

Hi

I am using a simply Sumifs formula, however this is across a data range that will change

This is the formula and works as is

Range("L6").Select
ActiveCell.Formula = "=SUMIFS('Apps Indicative Earnings Report'!$K$11:$K$73,'Apps Indicative Earnings Report'!$G$11:$G$73,'INVOICE CALC'!H6,'Apps Indicative Earnings Report'!$C$11:$C$73,'INVOICE CALC'!J6,'Apps Indicative Earnings Report'!$D$11:$D$73,'INVOICE CALC'!K6)"

however it wont always be row 73 it sums along. The rows may be greater or smaller next time

I have counted the numnber of rows in the data and stored this as in integer in variable "i"..

I am a real novice at VBA, so thought it may be as simple as replacing "$K$11:$K$73" with "$K$11:$K$ & i" - but of course it isnt

I could simply leave a massive range in the formula, but would rather not.

Any help would be appreciated

Thanks

Chris

 

[Peter_SSs]

I have counted the numnber of rows in the data and stored this as in integer in variable "i"..

Then try the following. Note that there is no need to select the cell to put a formula in it.

Range("L6").Formula = Replace("=SUMIFS('Apps Indicative Earnings Report'!$K$11:$K$#,'Apps Indicative Earnings Report'!$G$11:$G$#,'INVOICE CALC'!H6,'Apps Indicative Earnings Report'!$C$11:$C$#,'INVOICE CALC'!J6,'Apps Indicative Earnings Report'!$D$11:$D$#,'INVOICE CALC'!K6)", "#", i)

 

[cwilliams96]

Thanks Peter. I will give this a try. Thanks for the tip on not having to select a cell too

 

[cwilliams96]

Thanks Peter. This worked!!!!!!!!!!!!

 

[Peter_SSs]

Thanks Peter. This worked!!!!!!!!!!!!

You are welcome. Thanks for letting us know.