PDA

View Full Version : Solved: why is this VBA code slow?



ironj32
09-03-2008, 08:07 AM
The following code is used to count the # of cells in a range, and then put that value/count into cells on a different worksheet. It works properly, however it is pretty slow. I'm pretty new to VBA, and am wondering if there is a different/more proper way to write the code that would enable it to run quicker. Right now it takes probably just under 2 seconds to execute. Any thoughts?

Sub CampaignCount()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim cel As Range
Dim x1, x2, x3, x4, x5 As Long
Set ws = Worksheets("Summary")
Set ws2 = Worksheets("Detail")
For Each cel In ws2.Range("camp1")
x1 = x1 + 1
ws.Range("camp1count").Value = x1
Next
For Each cel In ws2.Range("camp2")
x2 = x2 + 1
ws.Range("camp2count").Value = x2
Next
For Each cel In ws2.Range("camp3")
x3 = x3 + 1
ws.Range("camp3count").Value = x3
Next
For Each cel In ws2.Range("camp4")
x4 = x4 + 1
ws.Range("camp4count").Value = x4
Next
For Each cel In ws2.Range("camp5")
x5 = x5 + 1
ws.Range("camp5count").Value = x5
Next
End Sub

Bob Phillips
09-03-2008, 08:14 AM
Sub CampaignCount()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim cel As Range
Dim x1, x2, x3, x4, x5 As Long

Application.Calculation = xlCalculationManual

Set ws = Worksheets("Summary")
Set ws2 = Worksheets("Detail")
For Each cel In ws2.Range("camp1")
x1 = x1 + 1
Next
ws.Range("camp1count").Value = x1
For Each cel In ws2.Range("camp2")
x2 = x2 + 1
Next
ws.Range("camp2count").Value = x2
For Each cel In ws2.Range("camp3")
x3 = x3 + 1
Next
ws.Range("camp3count").Value = x3
For Each cel In ws2.Range("camp4")
x4 = x4 + 1
Next
ws.Range("camp4count").Value = x4
For Each cel In ws2.Range("camp5")
x5 = x5 + 1
Next
ws.Range("camp5count").Value = x5

Application.Calculation = xlCalculationAutomatic
End Sub

Bob Phillips
09-03-2008, 08:16 AM
or evenb




Sub CampaignCount()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim cel As Range
Dim x1, x2, x3, x4, x5 As Long

Application.Calculation = xlCalculationManual

Set ws = Worksheets("Summary")
Set ws2 = Worksheets("Detail")

ws.Range("camp1count").Value = ws2.Range("camp1").Cells.Count
ws.Range("camp2count").Value = ws2.Range("camp2").Cells.Count
ws.Range("camp3count").Value = ws2.Range("camp3").Cells.Count
ws.Range("camp4count").Value = ws2.Range("camp4").Cells.Count
ws.Range("camp5count").Value = ws2.Range("camp5").Cells.Count

Application.Calculation = xlCalculationAutomatic
End Sub

ironj32
09-03-2008, 08:23 AM
perfect. Thanks xld!