How do you make Excel calculate a formula while Visual Basic is Running?

Question

I have a VB procedure that organizes data from several different spreadsheets. At one point, it goes through and adds the following formula to a sheet:

Sheets(WriteSheet).Range("Y" & WriteRow).Formula = "=IF(ISERROR(VLOOKUP(A2,'" & DelaySheet & "'!A:G,4,FALSE)),0,(VLOOKUP(A2,'" & DelaySheet & "'!A:G,4,FALSE))/86400)"

Once those fields are populated, I make a pivot table out of the results. My problem is that the pivot tables all end up with zeros as values because this vlookup formula is not calculated until after the VB procedure is done. This means that I have to right-click each pivot table and update the data to get the actual results.

Is there a way to make sure excel calculates the vlookup formula as the VB procedure is running? I've tried commands like Range("Z" & writerow).calculate and still can't get it to work.

Answer 1

Application.Calculate

Just throw the above line in at the time in execution that you want it to calculate, and you should be good to go.