Automatic recalculation problem

[rk8216]

Hi:
I have a VBA routine that has a formula like Worksheet("Sheet1").Range("A53").Formula = ""=MATCH(A52,AM6:AM50,0)".
When I am in manual recalculation mode and hit F9, this works perfectly. But when I am automatic recalculation mode, the function does not get evaluated. The formula does appear in the cell, but is not calculated. Any ideas will be greatly appreciated. Thanks

 

[Hui]

Firstly, Welcome to the Chandoo.org Forums
It shouldn't have a double "" in the middle

 

[rk8216]

Sorry that was my typo. The actual code is Worksheet("Sheet1").Range("A53").Formula ="=MATCH(A52,AM6:AM50,0)"
This does not work in automatic calculation mode. But works well in manual mode. For some reason Excel seems to skip this calculation. I read somewhere that if there are no volatile functions, Range.Calculate will trigger an automatic recalculation but does not calculate the cells referred to. Any ideas on how to overcome this? I am using Excel 2016 if that is of any help

 

[Ramesh]

Is there a typo again in your code pasted previously..
The code given by you is:

Worksheet("Sheet1").Range("A53").Formula ="=MATCH(A52,AM6:AM50,0)"

The correct code is:

Worksheets("Sheet1").Range("A53").Formula ="=MATCH(A52,AM6:AM50,0)"

please check the above and let us know..

 

[rk8216]

Having a bad day with typing. Yes Ramesh you are right. I have the correct code exactly as you have. Works in manual mode but not in automatic calculation mode. I am now trying to figure out if there is something else that is causing this problem. Thanks. Will be back in a day or two (there are several hundred lines to debug)