Sumif with dynamic range

[XL2008]

Hi everyone,

I'm having the hardest time finding a solution thats likely very simple and I'm only able to find very specific solutions to more complex issues. I'm using a vlookup that refers to cell ranges that vary in length.

I'm currently matching against the entire column which makes my code seem to bog down a bit.

Please Login or Register  to view this content.

This formula ends up being filled down across a fixed range of data, but the "CALC" tab varies. I just want to know what exactly I need to do in VBA so that I can have those ranges be dynamic instead of running the formula on the entire column.

[daffodil11]

Like this?

Please Login or Register  to view this content.

[oeldere]

You use a sumif with the whole column.

The formula will be much quicker if you make a range of it (e.g. "=SUMIF('CALC'!$C1:$C2500, 'RULES'!C2, 'CALC'!$B1:$B2500)

[XL2008]

Thanks Daffodil11 I think that may work. I have various ranges that are going to need this same code. Is this the simplest way to do it? IE I'd need to set up "Ender" for every time I have to pass a variable with a range.

oeldere, the range changes every time. One run can be 100 lines, the next could be over 10,000.

[daffodil11]

I'm stumped. Hmm..

[oeldere]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

while you take the whole column it will slow the formula.

so I suggest you take a defined range (e.g. B10000)