VBA not completing calculations before moving to the next process

[Zaddicus]

Okay so apologies if this doesn't make much sense, Mr Excel newbie here... I have a huge problem as I'm trying to automate a report and the report involved holds critical data for daily usage.

The problem I'm having is in column A on one of my worksheets (Tails) there is a formula array (Below) that basically pulls and lists all data that is considered a 'Tail' from another worksheet (Circuits)

{=IFERROR(IF(INDEX(Circuits!$B:$B,SMALL(IF("Tail"=Circuits!$A:$A,ROW(Circuits!$A:$A)),ROW(M1)))=0,"",INDEX(Circuits!$B:$B,SMALL(IF("Tail"=Circuits!$A:$A,ROW(Circuits!$A:$A)),ROW(M1)))),"")}

This function does work but takes a while due to the number of lines in the array (2000) and the rest of the table uses VLOOKUP to pull data based on the ID that the formula array lists so the worksheet looks something like this:

+--------------+---------+---------+-----------+ | ID | Age | Status | Fail Date | +--------------+---------+---------+-----------+ | ArrayFormula | Vlookup | Vlookup | Vlookup | | ArrayFormula | Vlookup | Vlookup | Vlookup | +--------------+---------+---------+-----------+

Problem is because there can be a max of 2000 rows in the array formula (So 22000 VLOOKUPS) I have disabled calculation on that worksheet and put a button in place for users to press if/when they need to calculate the tails. This allows them to use the rest of the workbook without excel trying to calculate every second.

This is where the problem comes in

The code that I wrote to basically force excel to complete calculation before proceeding with the formatting just doesn't work.

Sub Tails_Tracker_Setup()
Dim myMB As Integer

myMB = MsgBox("Tails calculation is CPU/RAM intensive and could take several minutes based on number of circuits (Limited to Max 2000) Do you want to proceed?", vbYesNo + vbQuestion, "Calculate Tails")

If Answer = vbYes Then
     Call Tails_Enable_Calc
     Application.Calculate
        
        If Not Application.CalculationState = xlDone Then
            DoEvents
        End If
          
     Call Tails_Hide_Actual
     Call Tails_Hide_10DD
     Call Tails_Disable_Calc
Else
     GoTo NoCalc
End If

NoCalc:

End Sub

So in order to understand I will explain what each call function does.
Call Tails_Enable_Calc - Enables calculation on the 'tails' sheet
Call Tails_Hide_Actual - Hides rows 5-2004 where the formula array has a value of ""
Call Tails_Hide_10DD - Hides rows 2007-2506 where a slightly different formula array has a value of ""
Call Tails_Disable_Calc - Disables calculation on the 'tails' sheet

What I'm finding is it doesn't actually calculate the array formula and spends 20-30 minutes doing god knows what before just hiding all the lines (Because the array formula hasn't updated so will return "")
I did do some googline but nothing has helped this even changing the If code to below:

        If Application.CalculationState = xlDone Then
            DoEvents
        End If
        
'I even tried:'

    Do Until Application.CalculationState = xlDone: DoEvents: Loop

If I manually enable calculation then it does update the array and vlookups, however it won't hide the blank rows and slows down the rest of the workbook which removes the point of automation.
Any help or guidance in the right direction will be greatly appreciated,

Thanks in advance

 

[jasonb75]

I can see no reason for needing DoEvents in the code, try removing those lines instead of changing them.

Also, reduce the size of your formulas. You say that you have 2000 rows, but your formulas are set up for over 1 million rows,only 0.2% of what is being calculated is needed, the other 99.8% is wasted processing!

 

[jasonb75]

Following up on my reply above, this formula only calcualtes the first 2000 rows of the Circuits worksheet and uses less steps than your existing formula, so should reduce calc time even more.

I've assumed numeric data in column B, if it is text then it will need some changes, but should still be more efficient than what you currently have.

You don't need to array confirm this one, the aggregate function creates the array automatically.

=IFERROR(1/(1/INDEX(Circuits!$B:$B,AGGREGATE(15,6,ROW(Circuits!$A$2:$A$2000)/(Circuits!$A$2:$A$2000="Tails"),ROWS(M$2:M2)))),"")

 

[steve the fish]

You could try experimenting with this. Do use a copy of your workbook though to do any test.

With Sheets("Tails")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    If lr > 1 Then .Range("A2:A" & lr).ClearContents 'clean destination sheet
End With

With Sheets("Circuits")
    If .AutoFilterMode Then .AutoFilterMode = False
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    With .Range("A1:A" & lr)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Tail"
        If .SpecialCells(xlCellTypeVisible).Count > 1 Then
            .Offset(1, 1).Resize(lr - 1).SpecialCells(xlCellTypeVisible).Copy
            Sheets("Tails").Range("A2").PasteSpecial xlPasteValues
        End If
    End With
    .AutoFilterMode = False
End With

Application.CutCopyMode = False

 

[Zaddicus]

@jasonb75

The formula array is only for listing the first 2000 results, so the first tail could be in row 471, the second could be row 22983 third in 104595 so unfortunately it does need to check the whole of column A until either all 2000 rows with the array are filled with data or returning a blank value (If there are not 2000 tails)

@steve the fish
I am so going to play with that today, using a copy paste would probably be easier than an array formula