Background
I have a macro number 1 which will
- populate a range with formulas
- turn the sheet to automatic calculation = true
When I run macro number 1, the cells will
- first be populated with formulas (the formula are copied and pasted)
- after several automatic refresh, the cells will go from #VALUE! to real values (because it takes time to fetch the data - it takes within 7 seconds)
I also have another macro number 2 which
- does some operations based on the real values generated by macro 1
P.S. both macros will have EnvOff in the beginning and EnvOn in the end, EnvOff means turns of automatic calculation and screen update = false, and vice versa.
The problem I face
I have difficulty linking 2 macros together in 1 single macro which calls the 2 functions I mentioned above in an error-free manner, because:
- I tried using
Application.Wait (Now + TimeValue("0:01:00"))，so what I did inmacro_number_3was:
macro_number_1
Application.Wait (Now + TimeValue("0:01:00")) 'which I thought will let values refresh
macro_number_2
But turns out the formulas in macro_number_1 is populated, but the values are not updating unless the whole marco_number_3 ends. So the Application.Wait (Now + TimeValue("0:01:00")) is useless here.
marco_number_3 returns a Type error because marco_number_2 do operations based on real values generated by macro_number_1 (Note: macro_number_1 populates the formula, but real values will show up only several seconds)
- After experimentation, I discover that the values based on populated formulas will only refresh and show up when no existing macro is running.
What I want to do
I want to link macro_number_1 and marco_number_2 together, so that marco_number_2 will run after the values in marco_number_1 have shown up. However, I can't use marco_number_3 to put them in a same function, nor can I use Application.Wait (Now + TimeValue("0:01:00")).
Any help would be greatly appreciated, thanks in advance.
