Something about this code is making Excel Freeze

[Trikageon]

Hey all! I think it might have something to do with the subsequent calculating, but this code seems to be the culprit when it comes to making my Excel seemingly freeze:

Dim rng As Range

With ActiveSheet.Range("E12:E600")
Set rng = .Find(What:="Total", LookAt:=xlWhole, LookIn:=xlValues)

If Not rng Is Nothing Then
Do
rng.FormulaR1C1 = "=SUMIF(C15,""Total""&R[-2]C[7],C7)"
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing
End If
End With
With ActiveSheet.Range("F12:F600")
Set rng = .Find(What:="Total", LookAt:=xlWhole, LookIn:=xlValues)

If Not rng Is Nothing Then
Do
rng.FormulaR1C1 = "=SUMIF(C15,""Total""&R[-2]C[6],C8)"
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing
End If
End With


How do I make this not slow everything down to a crawl? Thanks in advance?

 

[sulakvea]

delete Dim rng As Range part.

 

[Trikageon]

Thanks for your reply! When I do that I get an error that says, "Compile Error: Variable not defined" and it points to "rng ="

 

[Aaron Blood]

So it's not making "Excel Freeze", you just want to speed it up?

 

[snb_]

with ActiveSheet.columns(5)
   .autofilter 1,"Total"
   for each cl in .offset(1).specialcells(xlcelltypevisible)
     cl="=SUMIF(C15,""Total""&R[-2]C[7],C7)"
   next
   .autofilter
End with
 
with ActiveSheet.columns(6)
   .autofilter 1,"Total"
   for each cl in .offset(1).specialcells(xlcelltypevisible)
     cl="=SUMIF(C15,""Total""&R[-2]C[6],C8)"
   next
   .autofilter
End with

 

[Trikageon]

SNB - I'm getting the same "Variable not defined" error pointing to "c1"

Aaron - No, I'm having issues with freezing - the errors I'm mentioning are caught by VBA before I can even run the macro. If I didn't make any of the modifications graciously suggested by these users and ran it as-is, it would freeze.

 

[Aaron Blood]

SNB - I'm getting the same "Variable not defined" error pointing to "c1"

If you get that error it means you have "Option Explicit" specified at the top of your code and you have not dimensioned the variable Cl. From the looks of how you're using it, Cl is maybe a cell and should be dimensioned as a Range in your code.

Dim Cl as Range