Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My formulas will not calculate | Excel Worksheet Functions | |||
Formulas will not calculate | Excel Worksheet Functions | |||
How can I calculate Several Cell adding Formulas? | Excel Discussion (Misc queries) | |||
How do I calculate pro-rata formulas? | Excel Worksheet Functions | |||
formulas won't calculate | Excel Worksheet Functions |