Link to home
Start Free TrialLog in
Avatar of Kiwi-123
Kiwi-123

asked on

VB formula's

Is there a way I can refresh the formula's via a command button? I wanted to be able to refresh & then paste the values so that  the formula's would only update upon command.

I read that the subtotal function only extends to a 1000 lines, so have had to add the "60000" reference.

So this is one way I thought would help solve the lag problem when the filter is 'reset'


X2 =SUMPRODUCT(SUBTOTAL(2,OFFSET(Q9:Q60000,ROW(Q9:Q60000)-MIN(ROW(Q9:Q60000)),,1)),--(Q9:Q60000=5))

X3 =SUMPRODUCT(SUBTOTAL(2,OFFSET(Q9:Q60000,ROW(Q9:Q60000)-MIN(ROW(Q9:Q60000)),,1)),--(Q9:Q60000=4))

X4 =SUMPRODUCT(SUBTOTAL(2,OFFSET(Q9:Q60000,ROW(Q9:Q60000)-MIN(ROW(Q9:Q60000)),,1)),--(Q9:Q60000=3))

X5 =SUMPRODUCT(SUBTOTAL(2,OFFSET(Q9:Q60000,ROW(Q9:Q60000)-MIN(ROW(Q9:Q60000)),,1)),--(Q9:Q60000=2))

X6 =SUMPRODUCT(SUBTOTAL(2,OFFSET(Q9:Q60000,ROW(Q9:Q60000)-MIN(ROW(Q9:Q60000)),,1)),--(Q9:Q60000=1))

AA2 =SUBTOTAL(3,D9:D60000)

AA3 {=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9:F60000,ROW(F9:F60000)-MIN(ROW(F9:F60000)),,1)),--(F9:F60000="email"))&" Email"}

AA4 {=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9:F60000,ROW(F9:F60000)-MIN(ROW(F9:F60000)),,1)),--(F9:F60000="Vantive"))&" Vantive"}

AA5 {=INDEX(H9:H60000,MATCH(MAX(COUNTIF(H9:H60000,H9:H60000)),COUNTIF(H9:H60000,H9:H60000),0),)}

AA6 {=INDEX(I9:I60000,MATCH(MAX(COUNTIF(I9:I60000,I9:I60000)),COUNTIF(I9:I60000,I9:I60000),0),)}
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image
I think you may be confusing the fact that the autofilter dropdown only shows 1000 items (in xl2003 and prior) - SUBTOTAL works on whatever range you tell it to. I wouldn't recommend including more rows than necessary in formula like that.

What you may do is set calculation to manual and then use the Calculate method of the Range object:
Range("X2:X6").Calculate

Open in new window

for example.
Avatar of Kiwi-123
Kiwi-123

ASKER

Thanks Rorya, some good advice there.

Range("X2:X6").Calculate

Is there an example you could show me? How would I set the calculation to manual?
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial