[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# VB formula's

Posted on 2012-09-19
Medium Priority
445 Views
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),)}
0
Question by:Kiwi-123
• 2

LVL 85

Expert Comment

ID: 38416975
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
``````
for example.
0

Author Comment

ID: 38418854
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?
0

LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 38419792
application.calculation = xlcalculationmanual
or simply through the UI. ;)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
###### Suggested Courses
Course of the Month19 days, 22 hours left to enroll