Macro running slow

[Blunder1]

Hi All,

This is a fairly generic question... I have written a macro that slows down as it moves through the code. Is there a was i can tell what is causing this? Within the code i do reference and store around twenty ranges, but these are not big ( ie cells r2:r2000). I use these in array formulas later on in the code.

Any help would be much appreciated as i'm stuck on what to do next.

Thanks

Blunder

 

[VoG]

See if this makes a difference

Application.Calculation = xlCalculationManual
'
'your code
'
Application.Calculation = xlCalculationAutomatic

 

[FishingIsMoreFun]

This may also help with the "Lag", that is if you have not already done it.

Application.ScreenUpdating = False

 

[Smitty]

It would help if you posted the code.

 

[Blunder1]

Here is a snapshot from the code. I have two work books named wb1 and wb2. wb2 is my summary sheet where the formulas coutn teh data from wb1. I have named the sheet on wb1 'wa'.

j = 1
WS_Count = ActiveWorkbook.Worksheets.Count
For i = 3 To WS_Count
 
Sheets(i).Select
 Range("a65536").End(xlUp).Offset(1, 0).Value = CDate(iptbox)
 s = Range("a65536").End(xlUp).Row
 
  'C & C's
  LR = wd.Range("A65536").End(xlUp).Row
 Set rng = wd.Range("a2:a" & LR)
 strrng = rng.Address
 Range("c" & s).FormulaArray = "=count(if('[" & wb1.Name & "]" & wd.Name & "'!" & strrng & "='Main'!aa" & j & ",0))"
 
 
 'Fails
LR = wa.Range("a65536").End(xlUp).Row
Set rng = Range("r2:r" & LR)
strrng = rng.Address
Set rng = Range("b2:b" & LR)
grouprng = rng.Address
Range("e" & s).FormulaArray = "=sum(('[" & wb1.Name & "]" & wa.Name & "'!" & strrng & "<>""MATCHED"")*('[" & wb1.Name & "]" & wa.Name & "'!" & grouprng & "='Main'!aa" & j & "))"
Range("j" & s).FormulaArray = "=sum(('[" & wb1.Name & "]" & wa.Name & "'!" & strrng & "=""MATCHED"")*('[" & wb1.Name & "]" & wa.Name & "'!" & grouprng & "='Main'!aa" & j & "))"
Set rng = wa.Range("am2:am" & LR)
strrng = rng.Address
Range("g" & s).FormulaArray = "=sum(('[" & wb1.Name & "]" & wa.Name & "'!" & strrng & "=""NOK"")*('[" & wb1.Name & "]" & wa.Name & "'!" & grouprng & "='Main'!aa" & j & "))"
Set rng = wa.Range("an2:an" & LR)
strrng = rng.Address
Range("h" & s).FormulaArray = "=sum(('[" & wb1.Name & "]" & wa.Name & "'!" & strrng & "=""NOK"")*('[" & wb1.Name & "]" & wa.Name & "'!" & grouprng & "='Main'!aa" & j & "))"
Set rng = wa.Range("ao2:ao" & LR)
strrng = rng.Address
Range("i" & s).FormulaArray = "=sum(('[" & wb1.Name & "]" & wa.Name & "'!" & strrng & "=""NOK"")*('[" & wb1.Name & "]" & wa.Name & "'!" & grouprng & "='Main'!aa" & j & "))"
Set rng = wa.Range("ap2:ap" & LR)
strrng = rng.Address
Range("l" & s).FormulaArray = "=sum(('[" & wb1.Name & "]" & wa.Name & "'!" & strrng & "=""NOK"")*('[" & wb1.Name & "]" & wa.Name & "'!" & grouprng & "='Main'!aa" & j & "))"
Set rng = wa.Range("aq2:aq" & LR)
strrng = rng.Address
Range("m" & s).FormulaArray = "=sum(('[" & wb1.Name & "]" & wa.Name & "'!" & strrng & "=""NOK"")*('[" & wb1.Name & "]" & wa.Name & "'!" & grouprng & "='Main'!aa" & j & "))"
Set rng = wa.Range("ar2:ar" & LR)
strrng = rng.Address
Range("o" & s).FormulaArray = "=sum(('[" & wb1.Name & "]" & wa.Name & "'!" & strrng & "=""NOK"")*('[" & wb1.Name & "]" & wa.Name & "'!" & grouprng & "='Main'!aa" & j & "))"
Set rng = wa.Range("as2:as" & LR)
strrng = rng.Address
Range("p" & s).FormulaArray = "=sum(('[" & wb1.Name & "]" & wa.Name & "'!" & strrng & "=""NOK"")*('[" & wb1.Name & "]" & wa.Name & "'!" & grouprng & "='Main'!aa" & j & "))"

Thanks again

Blunder