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

# 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
• 3
• 2
1 Solution

Commented:
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
``````

Thomas
0

Author 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

Commented:
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
``````

Thomas
0

Author Commented:
Thanks Thomas!
0

Commented:

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.