Subroutine sets range.formula to call a function

i have the following problem:
1. I have a subroutine that populates cell formulas (recreating a manual model i built)

Please Login or Register  to view this content.

2. this will place a formula in the cell that calls "=numSlots(H2,K2,M1)"

3. upon debugging, i find that the subroutine, right after it sets the formula for that cell, breaks
4. and excel begins to run the formula which was just set on the cell.
5. this formula then breaks because the cells it needs as arguments don't have values yet!

Question:

when I set the formula for a cell, Can I do something prior to that so that the worksheet doesn't try to calculate the formula?

i'm looking, and have been looking, for something along the lines of "Range("x").calculations = FALSE"

I tried setting my workbook to do manual calculations but it just keeps on calculating the formula in that cell automatically!


any help is appreciated!

--------
excel 2010 Mac

Try this before your macro starts:

Please Login or Register  to view this content.

The don't forget to turn it back on:

Please Login or Register  to view this content.

You can then force a calculation with:

Please Login or Register  to view this content.

Thanks! i just tried it in 2 ways. I created a parent Subroutine "control" that calls my subroutine "defineRanges" that populates the formula that calls the function

Please Login or Register  to view this content.

then I also tried, just within "defineRanges" to make the Application.Calculation changes at the very beginning and at the very end.
Didn't work in either case.

then I looked into populating the value instead of the formula but from the other forums, it seems like that will just yield the same result.
i just need a way to finish running the subroutine before any of these formulas calculate.

thanks again.

Originally Posted by weirdaze

Thanks! i just tried it in 2 ways. I created a parent Subroutine "control" that calls my subroutine "defineRanges" that populates the formula that calls the function

Please Login or Register  to view this content.

then I also tried, just within "defineRanges" to make the Application.Calculation changes at the very beginning and at the very end.
Didn't work in either case.

then I looked into populating the value instead of the formula but from the other forums, it seems like that will just yield the same result.
i just need a way to finish running the subroutine before any of these formulas calculate.

thanks again.

I Just noticed that this actually works earlier in the code when I refer to a cell that is already populated.
the problem above occurs because i'm referring to a cell that has a validation that has not been activated (drop down)

Please Login or Register  to view this content.

when I try to run he subroutine, the reason it breaks is because one of my operations has an error from referring to the empty cell.

I will try to get the validation to populate a default value so I can move forward.