troubleshooting Question

Speed up VBA code

Avatar of MirageSF
MirageSF asked on
VBA
5 Comments1 Solution89 ViewsLast Modified:
Hi,

I am using... the following code to scan 52 sheets completely for the number 85099055 and if it finds that number to sum up the 7 values to its right of where it was found on each sheet.

Works great, however once the sheet contains about 20k plus of other values it slows down to a grind, is there a way to speed this up dramatically...  If it helps the Range can be reduced to just B8:B60, G8:G60 AND V8:V60 and the number will not be on every sheet and if it is will just appear once per sheet.  Thanks

Number = 85099055
Total=0
      For Idx = 1 To 52
            With Sheets(Idx).Cells
                Set c = .Find(Number, .Cells(.Rows.Count, .Columns.Count), lookat:=xlWhole)
                If Not c Is Nothing Then Total = Total + WorksheetFunction.Sum(c.Offset(0, 2).Resize(1, 7))
            End With
        Next
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros