VBA apparently won't wait until the previous formula is completed

[fraudit]

I've created a macro that's supposed to:
1. get the account numbers from another sheet, together with their transactions
2. de-duplicate account numbers
3. calculate the number their transactions, dependent on the transaction status (pass, review, reject)
4. allocate customers to the sales channels (online, offline, telesales and if more than one - multi)

The macro works almost correctly - when the file is small, it runs perfectly, but when the file gets bigger (approx 100k transactions), I'm only getting a couple of hundreds of rows correctly prepopulated and the remaining values are zeros.

I believe then the next function runs before the previous one had the chance to complete and hence those zeros...

Can you advise me what to do in order to make all the embedded functions be able to produce all result?

Here's the relevant part of the code:

' DE-DUPLICATE CUSTOMERS


Dim LastUndupedRow As Long
LastUndupedRow = ActiveSheet.UsedRange.Rows.Count
 
    Range("A1:A" & LastUndupedRow).RemoveDuplicates Columns:=1, Header:=xlYes


' CHECK STATUSES


Dim LastRow As String
LastRow = ActiveSheet.UsedRange.Rows.Count


    Range("B2:B" & LastRow).FormulaR1C1 = _
        "=COUNTIFS(TMX_Data!C15,Customers_Stats!RC1,TMX_Data!C9,""pass"")"
    Range("C2:C" & LastRow).FormulaR1C1 = _
        "=COUNTIFS(TMX_Data!C15,Customers_Stats!RC1,TMX_Data!C9,""review"")"
    Range("D2:D" & LastRow).FormulaR1C1 = _
        "=COUNTIFS(TMX_Data!C15,Customers_Stats!RC1,TMX_Data!C9,""reject"")"
    Range("E2:E" & LastRow).FormulaR1C1 = _
        "=IF(RC[-3]>0,""pass"",IF(RC[-2]>0,""pass"",""reject""))"
    Range("F2:F" & LastRow).FormulaR1C1 = _
        "=COUNTIFS(TMX_Data!C15,Customers_Stats!RC1,TMX_Data!C27,""default"")"
    Range("G2:G" & LastRow).FormulaR1C1 = _
        "=COUNTIFS(TMX_Data!C15,Customers_Stats!RC1,TMX_Data!C27,""offline"")"
    Range("H2:H" & LastRow).FormulaR1C1 = _
        "=COUNTIFS(TMX_Data!C15,Customers_Stats!RC1,TMX_Data!C27,""telesales"")"
    Range("I2:I" & LastRow).FormulaR1C1 = _
        "=COUNTIFS(TMX_Data!C15,Customers_Stats!RC1,TMX_Data!C27,""test"")"
    Range("J2:J" & LastRow).FormulaR1C1 = _
    "=IF(OR(AND(RC6>0,RC7>0),AND(RC6>0,RC8>0),AND(RC7>0,RC8>0)),""multi"",(IF(AND(RC6>0,RC7=0,RC8=0),""online"",(IF(AND(RC6=0,RC7>0,RC8=0),""offline"",""telesales"")))))"




With Range("B2:J" & LastRow)
    .Value = Range("B2:J" & LastRow).Value
    .HorizontalAlignment = xlCenter
    .ColumnWidth = 10#
End With

 

[BiocideJ]

Try adding

Application.Calculate

after entering all of the formulas to tell Excel to process all calculations.

 

[fraudit]

Understood, thank you!

Calculations take a massive number of time, due to the sheet data amount. Do you think if I use the VBA WorksheetFunction.Countifs it would go any faster?

I've been trying this, but somehow I can't make it work - the syntax is continuously wrong . I've ended up with this:

Range("B2:B" & LastRow).Value = Excel.WorksheetFunction.Countifs=(Sheets("TMX_Data").Range("O:O"), Range("A2:A" & LastRow), Sheets("TMX_Data").Range("I:I"), "pass")

 

[BiocideJ]

You could also try this more targeted calculation method.

Range("B2:J" & LastRow).Calculate

This will only help if you have alot of formulas not in the B:J range.

I don't expect you will have much faster calculation using WorksheetFunction as it basically still does the same thing.
Ultimately, you will have to pick which is more problematic: Working with uncalculated values or waiting for the values to calculate.

 

[Norie]

Try setting calculation to manual at the start of the code, then after the cells have been populated with formulas set it back to automatic.

PS You might also want to simply try F9 after the code has run to recalculate everything.