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?

 

[whyme6181]

Thank you for your help so far, but I am still getting invalid results from the formula (i.e. 0) Here is the updated code so far:

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).Cells(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

It's weird, because when I enter the formula manually it processes correctly. I think I need something in between the entering of the formula and the applying of the filter to ensure that all of the cells have calculated. Does that make sense?