VBA Runtime error 6: overflow on one calculation, but other calculations work

Question

I'm trying to run the following code, but I'm getting a runtime error 6: overflow for the last calculation that I am trying to do. The program stops when it reaches MLtot = MLwin / (MLwin + MLloss). The other two calculations can be done without issues.

I've also tried setting my variable to different data types to solve the issue, but without luck.

Sub AutoWinPercent()

    Dim Swin As Variant, Sloss As Variant, OUwin As Variant, OUloss As Variant
    Dim MLwin As Variant, MLloss As Variant
    Dim Stot As Double, OUtot As Double, MLtot As Double

    For i = 1 To 17
        Swin = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("Y3:Y258"), "WIN")
        Sloss = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("Y3:Y258"), "LOSS")
        OUwin = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("Z3:Z258"), "WIN")
        OUloss = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("Z3:Z258"), "LOSS")
        MLwin = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("AA3:AA258"), "WIN")
        MLloss = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("AA3:AA258"), "LOSS")
        
        j = i + 2   'sets the starting row of where the percentages will be placed.
        
        Stot = Swin / (Swin + Sloss)
        OUtot = OUwin / (OUwin + OUloss)
        MLtot = MLwin / (MLwin + MLloss)
        
        Range("AC" & j).Value = Stot     'Spread
        Range("AD" & j).Value = OUtot    'OU
        Range("AE" & j).Value = MLtot  'ML
        
    Next i

End Sub

Answer 1

Given the overflow error is due to division by zero, consider a user defined function to check zeroes in denominator and conditionally handle calculation as needed. Avoid the On Error Resume Next quick fix as it will suppress other errors and makes it difficult to debug issues.

Additionally, as best practice in VBA, be sure to qualify all objects (i.e., .Range to Worksheet, Worksheet to Workbook). Below uses With block. Please adjust for actual sheet name. Also, Dim all variables used such as i and j (unless publicly declared).

Function (place in same module as Sub or standard module)

Function CalcExpression(numerator As Variant, denominator As Variant) As Variant
   Dim val As Variant

   If denominator = 0 Then
       val = ""
   Else 
       val = numerator / denominator 
   End If

   CalcExpression = val   
End Function

Subroutine

Sub AutoWinPercent()
On Error Goto ErrHandler

    Dim Swin As Variant, Sloss As Variant
    Dim OUwin As Variant, OUloss As Variant 
    Dim MLwin As Variant, MLloss As Variant 
    Dim Stot As Variant, OUtot As Variant, MLtot As Variant 
    Dim i As Long, j As Long

    With ThisWorkbook.Worksheets("myWorksheet") 
        For i = 1 To 17 
             Swin = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("Y3:Y258"), "WIN")
             Sloss = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("Y3:Y258"), "LOSS") 
             OUwin = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("Z3:Z258"), "WIN") 
             OUloss = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("Z3:Z258"), "LOSS") 
             MLwin = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("AA3:AA258"), "WIN") 
             MLloss = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("AA3:AA258"), "LOSS") 

             Stot = CalcExpression(Swin, (Swin + Sloss))
             OUtot = CalcExpression(OUwin, (OUwin + OUloss))
             MLtot = CalcExpression(MLwin, (MLwin + MLloss))
              
             j = i + 2 'sets the starting row of where the percentages will be placed. 

             .Range("AC" & j).Value = Stot 'Spread 
             .Range("AD" & j).Value = OUtot 'OU 
             .Range("AE" & j).Value = MLtot 'ML
         Next i
    End With

ExitHandler:
    Exit Sub

ErrHandler:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
    Resume ExitHandler
End Sub