VBA not completing calculations before moving to the next process

Zaddicus

New Member
Joined
Dec 6, 2019
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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:

VBA Code:
        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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
You could try experimenting with this. Do use a copy of your workbook though to do any test.

VBA Code:
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

New Member
Joined
Dec 6, 2019
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
@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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,824
Messages
5,627,108
Members
416,222
Latest member
coloray13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top