Formula won't calculate properly.

[whyme6181]

I have a formula that works fine on a bunch of tools that I manage. However, when I attempt to use it on this one specific tool it will not calculate fully before the next step of the macro. Because of this instead of applying the formula to the selected range and then filtering off of that result I now have to go row by row calculating the formula in order to get an accurate result. If I use the same formula outside of the macro it works properly. Are there any steps I can include in my code to ensure that it will not move on to the next step until the formula is completely calculated?

 

[RoryA]

If you don't show us the code it's quite hard for us to fix it...

 

[whyme6181]

I was just hoping to get a general answer, but here is the code in question.

Sub DupCheck(ra1, rHDR, rCase, rDisp)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
StepNum = StepNum + 1
Application.StatusBar = "Validating Case Count (Step " & StepNum & " of 18)"

Dim rw As Range

'''DoEvents
    rHDR.Offset(0, 1).Select
    If Cells(rHDR.row, ActiveCell.Column) = "Case Count" Then
    Else
        Trulcol = Trulcol + 1
        rCase.Offset(0, 1).EntireColumn.Insert
        rCase.Offset(0, 1).EntireColumn.NumberFormat = "General"
        rCase.Offset(0, 1).EntireColumn.Interior.Color = 65535
        Cells(rHDR.row, rCase.Column + 1) = "Case Count"
    End If
    
    'Use Filter instead of do loop

    'lcol = Cells(Hdr.row, Columns.Count).End(xlToLeft).Column
    Set ra1 = Range(Cells(rHDR.row + 1, ActiveCell.Column), Cells(Trulrow, ActiveCell.Column))
    ra1.Formula = "=CountIf(" & rCase.Address & ", " & ra1.Offset(0, -1).Address(False, True) & ")"
    If Application.WorksheetFunction.CountIf(Columns(ActiveCell.Column), ">1") > 0 Then
        ActiveSheet.Range(Cells(rHDR.row, 1), Cells(Trulrow, Trulcol)).AutoFilter Field:=rCase.Column + 1, Criteria1:=">=2"
        
        If Application.WorksheetFunction.CountA(Range(Cells(Hdr.row, ActiveCell.Column), Cells(Trulrow, ActiveCell.Column)).SpecialCells(xlCellTypeVisible)) > 1 Then
            Set rw = Range(Cells(rHDR.row + 1, ActiveCell.Column), Cells(Trulrow, ActiveCell.Column)).SpecialCells(xlCellTypeVisible)
            pIssue = "Duplicate Case ID"
            rw.Interior.ColorIndex = 22
            rw.Offset(0, -1).Interior.ColorIndex = 22
            Call iLog3(rw, sHome, pIssue)
        End If
        ActiveSheet.ShowAllData
    End If   
    
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[RoryA]

Are you using a version of excel with dynamic arrays?

You've turned off calculation at the start of the code, which shouldn't be an issue, given the way you are populating the formulas, unless of course the application was already in manual calculation mode. You might want to put calculation to automatic after populating the formula and before doing the filtering.

 

[whyme6181]

No. Just a bunch of ranges that are defined at the start of the run of the macro. This subroutine works fine in other examples, even examples that have more rows than the one in question. However, when I use it on a particular report that has over 400,000 rows it only partially calculates. This leaves me with inaccurate results when I try to filter based on the result of the formula.

 

[RoryA]

No. Just a bunch of ranges that are defined at the start of the run of the macro

That doesn't answer my question. What version of Excel are you using?

What exactly does "partially calculates" mean? It calculates the first cell and repeats the result, or it calculates some cells at random, or something else?

 

[whyme6181]

Hi. sorry, I didn't understand your question correctly. I am currently running Microsoft 365 64 bit version. When I say "partially calculates" what I mean is that after the formula is applied to the range for some cells it will actually calculate, but for others it will just show the base value. For example, we have had cases where the value was 0. This is impossible since it is counting how many times the value in the cell is in the rest of the column which will always return a value of 1 or greater. So, the only theory as to why this is happening is that once the formula is applied instead of actually calculating it puts in a placeholder value until it finishes calculating, but since it doesn't finish calculating before the next step the placeholder is never replaced with the actual value. While writing this I realized that I also forgot the step where we take the value of the cell and replace the formula with that value instead, because as the rest of the subroutines run the spreadsheet becomes so bloated with formulas that it drastically slows down the performance.

So, my original question was I have a formula that is applied to a range. I want the formula to calculate and then I want to replace the range with the value from the formula, however it seems like I need to pause the macro long enough for the 430,000 formulas to calculate before replacing the values and in its current state it doesn't appear to do that. Is there some code that could be put in to wait until the calculation is finished?

 

[RoryA]

You could try putting calculation back to automatic before applying the formula. I'd also amend the formula to:

ra1.Formula = "=CountIf(" & rCase.Address & ", " & ra1.Offset(0, -1).Cells(1).Address(False, True) & ")"

 

[whyme6181]

Sorry if this is a stupid question, but what does the addition of ".Cells(1)" do?

 

[RoryA]

It makes it refer specifically to the cell to the left in each row, rather than using the entire column in every cell. In other words, instead of putting

=COUNTIF($B$2:$B$100,$B2:$B100)

you put

=COUNTIF($B$2:$B$100,$B2)