VBA for IF and CountIF Statements

[LJones60]

I have a worksheet that has a lot of formulas and every time i'm clicking around the sheet it hangs as it calculates the formulas. The majority of my formulas are IF or CountIF statements such as the following

=IF(AD13>0,1,0)

=IF(G13>0,(G13-F13)/7,0)

=COUNTIF(B4:B123,AF3)

The first and second if statements above is in every cell within the range AD4:AD123 and AE4:AE123

I have very little knowledge of writing VBA so was wondering if there is some VBA code that can easily carry out the calculations for the if and CountIF statements above that will reduce the amount of times the spreadsheet hangs whilst its calculating the formulas.

Any help is greatly appreciated. Thanks

 

[WinteE]

The question is whether this slow down is caused by the formulas or by your PC/laptop.

I guess you'd better check the RAM of your computer first.

Otherwise you can stop the automatic calculation Tools, Options, Tab Calculation, check Manual

 

[LJones60]

The cause of the hanging is probably because i'm on a networked laptop with a few thousand users.

Thanks for the advice on turning off auto calculation.

If VBA would help speed things up then I would still like to input some along with a bit of learning at the same time.

 

[LJones60]

Can anyone help with the VBA?

 

[Myproblem]

Can anyone help with the VBA?

try something like this:
range("your target cell") = "=IF(AD13>0,1,0)"
and keep going like this example if it is working

 

[mikerickson]

Excel's normal sheet formulas are faster than the VBA equivalent.
Excel formulas are written and evaluated by routines that are much, much faster than VBA.

You could optimize the first formula by =--(AD13>0)
The second could be re-written =(G13>0)*(G13-F13)/7

But with that number of cells, you shouldn't notice any slow response.
Clicking on cells, changing the selection, but not the changing the contents of cells does not cause a calculation. So no number of formulas will slow because of clicking on cells, only changing a cell's value can cause a pause. (Unless there is a VBA SelectionChange event)