Solved

Command button code

Posted on 2011-03-09
286 Views
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
Question by:Gazza83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 39

Expert Comment

ID: 35087367
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 Comment

ID: 35087883
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

LVL 39

Accepted Solution

nutsch earned 500 total points
ID: 35088387
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 Closing Comment

ID: 35088944
Thanks Thomas!
0

LVL 39

Expert Comment

ID: 35088966

Thomas
0

Featured Post

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.