View Full Version : Formulas with Minus

09-21-2016, 04:27 AM
Following problem I found no solution on:

I have a spread sheet where insert formulas. Some of the formulas have a minus in there. This ones are not calculating after they have been inserted. The weird thing is that if I click on the cell go into the data entry bar and click enter, the in the cell starts to work.

An example of minus formula:

Range("AT3").Formula = "=(AA3-AS3)/AA3"
Range(Cells(4, 46), Cells(3 + i, 46)).Select

I have tried so far:

ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True


Many Thanks, Jens

Kenneth Hobs
09-21-2016, 08:05 AM
Please paste code between code tags. Type them or insert by clicking the # icon on a reply toolbar.

After a run, check the Immediate Window to see the values printed by Debug.Print. I suspect that you calculation mode is not set as you think. Of course you can force sheet calculation.

Sub Main()
Dim i As long

Range("AT3").Formula = "=(AA3-AS3)/AA3"
Range("AT3").Copy Range(Cells(4, 46), Cells(3 + i, 46))

Debug.Print Application.Calculation, xlCalculationSemiautomatic, xlCalculationManual, xlCalculationAutomatic

End Sub

09-21-2016, 08:54 AM
Hi Kenneth, thanks for feedback, the solution was resetting the data typ. Then the formulas started to work. Thanks Jens!