• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Command button code

Please can you help, I am hoping to do away with the formulas held within E9:E917 from Sheet3 "Location.

I was hoping to use a command button that could calculate the occurrence of the listed codes (C9:C917) on sheet1 H14:H873

It would then display these in descending order.

The difficulty is that the formulas currently filter the data by the listed dates in F2:F3 sheet3 and matches this to the dates listed in B14:B873 sheet1.





help.xls
0
Gazza83
Asked:
Gazza83
  • 3
  • 2
1 Solution
 
nutschCommented:
Here, this code will put in the formula, then copy /paste values it so the formula disappears when you're not updating:

Sub Macro2()
'
With Sheets("Location").Range("E9:E917")
    .FormulaR1C1 = _
        "=SUMPRODUCT((data!R14C2:R20000C2+0>=R2C6)*(data!R14C2:R20000C2+0<=R3C6)*(data!R14C8:R20000C8=RC3))"
    .Value = .Value
End With
End Sub

Open in new window


Thomas
0
 
Gazza83Author Commented:
Thanks Thomas, this seems perfect! I've run the code and it took little to no processing time; whereas the old way was taken ages.

Is there a way I could sort the range highest-Lowest, maybe I could use another command button?
0
 
nutschCommented:
Something like this?

Sub Macro2()

With Sheets("Location").Range("E9:E917")
    .FormulaR1C1 = _
        "=SUMPRODUCT((data!R14C2:R20000C2+0>=R2C6)*(data!R14C2:R20000C2+0<=R3C6)*(data!R14C8:R20000C8=RC3))"
    .Value = .Value
End With

With Sheets("Location").Range("C9:E917")
    .Sort Key1:=Range("E9"), Order1:=xlDescending
End With

End Sub

Open in new window


Thomas
0
 
Gazza83Author Commented:
Thanks Thomas!
0
 
nutschCommented:
Glad to help. Thanks for the grade.

Thomas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now