calculate cell formulas during VBA sub
 

Is there a way to get cell formulas (preferably certain formulas in a cell or
range) to run at a designated point in a VBA subroutine ?

I have a straightforward table lookup process (formula, array, etc) that
would take a LOT of VBA code to accomplish equivalent function.

Cheers. dj

calculate cell formulas during VBA sub
 

DonJ-Austin wrote:
Is there a way to get cell formulas (preferably certain formulas in a cell or
range) to run at a designated point in a VBA subroutine ?

I have a straightforward table lookup process (formula, array, etc) that
would take a LOT of VBA code to accomplish equivalent function.

Cheers. dj

If you have calculation set to manual (tools - options - calculation
tab), your formulas can be made to run using Application.Calculate or
Worksheets("Sheet1").Calculate, for example. VBA can set the
calculation mode using Application.Calculation =
[xlCalculationAutomatic, xlCalculationManual or
xlCalculationSemiautomatic]. If your table is a data table, (from the
Data menu), semiautomatic will require manual calculation for formulas
in data tables. That might be a way to manually run formulas only in
certain cells. I have never used Data Tables, so I don't know any more
details.

HTH
Len

calculate cell formulas during VBA sub
 

Try inserting the line "Calculate" without the quotes when you want the
formulas to be figured.

for example:

Sub runcalc()
ActiveCell.FormulaR1C1 = "5"
Calculate
Range("A7").Select
ActiveCell.FormulaR1C1 = "4"
Range("A8").Select
End Sub

this puts a value of 5 in the active cell calculates the worksheet then
continues with the code.

Hope it helps.


--
goober
------------------------------------------------------------------------
goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
View this thread: http://www.excelforum.com/showthread...hreadid=494875