Stop macro until calculations complete?

[JP Romano]

Hello - I'm working on a fair sized spreadsheet which needs to execute a few api calls to retrieve data, then compare that retrieved data to similar points from another source. What I'd like to do, if possible, is prevent the macro from proceeding with the next formula on the sheet until the calculations are completed on the first.

I have very little VB experience, and no other programming languages, so please forgive me if there's an obvious way to do this which I've overlooked. I'm using Office 2007 on Win XP. The tab in question is 28,055 rows and 7 columns. I'm not sure if it'll help, but some of the code for this operation is below.
FYI, I've been asked to complete this with as little user interaction as possible, so I'd like to consider a userform, messagebox, or other indicator a last resort.

I've added a few "Calculate" lines where I think they need to be (trying to calculate certain columns together instead of one at a time)... this is where I'd like to be able to wait before proceeding.

Thank you for any assistance!


' formula Macro
'
' to fill the Contributor Value column
Dim s As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("Comparisons").Select
Range("B2").Select

'Column B: Formula to match contributor ticker with cross reference table

ActiveCell.formula = _
"=IF(ISERROR(VLOOKUP(A2,XRef!A:B,2,FALSE)),""No Data Available"",(VLOOKUP(A2,XRef!A:B,2,FALSE)))"
Selection.AutoFill Destination:=Range("B2:B28055"), Type:=xlFillDefault

Calculate

'Column C: Looksup the contributor's data

Range("C2").Select
ActiveCell.formula = _
"= IF(ISERROR(VLOOKUP(B2,Contributor!A:AZ," & coltouse & ",FALSE)),""No Data Available"",(VLOOKUP(B2,Contributor!A:AZ," & coltouse & ",FALSE)))"
Selection.AutoFill Destination:=Range("C2:C28055"), Type:=xlFillDefault

'Column D: Looksup the API value

Range("D2").Select
ActiveCell.formula = _
"=IF(ISERROR(VLOOKUP(A2&"" Equity"",RawData!A:I,6,FALSE)),""No Data Available"",VLOOKUP(A2&"" Equity"",RawData!A:I,6,FALSE))"
Selection.AutoFill Destination:=Range("d2:d28055"), Type:=xlFillDefault

Calculate

'Column E: Matches the Contributor and API values

Range("E2").Select
ActiveCell.formula = _
"=IF(C2=D2,""Pass"",""Fail"")"
Selection.AutoFill Destination:=Range("E2:E28055"), Type:=xlFillDefault

Calculate

'Column F: If the match fails, execute secondary API query for annualized data

Range("f2").Select
ActiveCell.formula = _
"=IF(E2=""Fail"", BDP(A2& "" Equity"", ""best_eps"",""best_fperiod_override"",""1fy"",""best_data_source_override"",XRef!$D$1,""best_consolidated_override"",""C""),""Pass"")"
Selection.AutoFill Destination:=Range("F2:F28055"), Type:=xlFillDefault

'Column G: Matches the new annual data with the API value returned in
column D

Range("G2").Select
ActiveCell.formula = _
"=IF(E2=""Fail"",IF(D2=F2,""Pass"",""Fail""),""Pass"")"
Selection.AutoFill Destination:=Range("G2:G28055"), Type:=xlFillDefault

Calculate

 

[Jonmo1]

As far as I know, that should be the default behavior of code. If calculation runs, vba code waits for it to complete then continues...

also, It looks like you're entering a formula in a single cell, then filling it down. If you enter the formula in ALL the cells at the same time, they will all calculate at the time they are entered without you needing to tell it to calculate...

so instead of

Range("B2").Select
 
'Column B: Formula to match contributor ticker with cross reference table
 
ActiveCell.formula = _
"=IF(ISERROR(VLOOKUP(A2,XRef!A:B,2,FALSE)),""No Data Available"",(VLOOKUP(A2,XRef!A:B,2,FALSE)))"
Selection.AutoFill Destination:=Range("B2:B28055"), Type:=xlFillDefault
 
Calculate

you could put the formula in the whole range in one shot...

Range("B2:B28055").formula = _
"=IF(ISERROR(VLOOKUP(A2,XRef!A:B,2,FALSE)),""No Data Available"",(VLOOKUP(A2,XRef!A:B,2,FALSE)))"

Hope that helps...

 

[JP Romano]

Wow...thanks for the speedy response. That seems to have done the trick! Not sure why the formulas weren't working before, but now that I'm doing it your way, everything looks great.
Thanks again!!!!!!