Why am I getting an 'Overflow' error, now?

[Groves22]

Hello..

I started building a macro on Friday. I got to a certain point, and had to leave for the weekend. I tested up through the point, and everything worked fine. Now when I tested it this morning, I am getting an Overflow error in a step that worked fine on Friday.

I have not changed anything except added some more code way after this point.

Why is this happening now, how can I fix this??

Thanks

The last line is where the error is occuring...

Public OHNBPA As String, OHCVPA As String, OHNBHO As String, OHCVHO As String
Public OHNYear As String, OHNMonth As String, OHNDay As String, OHCYear As String, OHCMonth As String, OHCDay As String

Sub agency_split()

With Workbooks("Agents Macro").Sheets(1)
    OHNBPA = .Range("OHNBPA")
    OHCVPA = .Range("OHCVPA")
    OHNBHO = .Range("OHNBHO")
    OHCVHO = .Range("OHCVHO")
End With

If OHNBPA = "Yes" Or OHCVPA = "Yes" Or OHNBHO = "Yes" Or OHCVHO = "Yes" Then
    Call run_OH
End If

End Sub

Sub run_OH()

'***COLLECTS DATES FOR PROFILES***
Dim response

MsgBox "Please provide the dates for the profiles.", vbOKOnly, "Dates needed."

If OHNBPA = "Yes" Or OHNBHO = "Yes" Then
101
    OHNYear = InputBox("Please enter year for Ohio NB:", "Year", "0000")
    OHNMonth = InputBox("Please enter month (##) for Ohio NB:", "Month", "00")
    OHNDay = InputBox("Please enter day (##) for Ohio NB:", "Day", "00")
    
    response = MsgBox("Is " & OHNMonth & "/" & OHNDay & "/" & OHNYear & " the correct date?", vbYesNo, "Verify")
    
        If response = 7 Then GoTo 101

End If

If OHCVPA = "Yes" Or OHCVHO = "Yes" Then
102
    OHCYear = InputBox("Please enter year for Ohio Conv:", "Year", "0000")
    OHCMonth = InputBox("Please enter month (##) for Ohio Conv:", "Month", "00")
    OHCDay = InputBox("Please enter day (##) for Ohio Conv:", "Day", "00")
    
    response = MsgBox("Is " & OHCMonth & "/" & OHCDay & "/" & OHCYear & " the correct date?", vbYesNo, "Verify")
    
        If response = 7 Then GoTo 102

End If

'***COPY DATA FROM PROFILE TO VALUE PROFILE***
If OHNBPA = "Yes" Or OHNBHO = "Yes" Then
    Workbooks.Open ("G:\ACIC New Product Profiles\OH\Macro Items\OH New Business - Template.xls")
End If

If OHNBPA = "Yes" Then
    Workbooks.Open ("G:\ACIC New Product Profiles\OH\Ohio NB Auto Profile " & OHNMonth & OHNDay & OHNYear & ".xls")
    Workbooks.Open ("G:\ACIC New Product Profiles\OH\Macro Items\Macro Scrap.xls")
End If

Set NB_Temp = Workbooks("OH New Business - Template.xls")
Set OHNBPA_Prof = Workbooks("Ohio NB Auto Profile " & OHNMonth & OHNDay & OHNYear & ".xls")
Set Agt_Macro = Workbooks("Agents Macro.xls")
Set Scrap = Workbooks("Macro Scrap.xls")

OHNBPA_Prof.Sheets("Profile").Range("Agt_Lkup").Copy
Agt_Macro.Sheets(1).Range("J1").PasteSpecial xlPasteValues

Dim agt_btm As Long
agt_btm = Agt_Macro.Sheets(1).Range("J1").End(xlDown).Row

Dim i As Long, j As Long
i = 1
j = 1
Do Until i > agt_btm
    Scrap.Sheets("Auto").Cells.Delete
    Agt_Macro.Sheets(1).Range("J" & i).Copy
    OHNBPA_Prof.Sheets("Profile").Range("B4").PasteSpecial xlPasteValues
    Calculate
    OHNBPA_Prof.Sheets("Profile").Cells.Copy
    Scrap.Sheets("Auto").Cells.PasteSpecial xlPasteValues
    OHNBPA_Prof.Sheets("Profile").Cells.Copy
    Scrap.Sheets("Auto").Cells.PasteSpecial xlPasteFormats
    Scrap.Save
    
    Do Until Scrap.Sheets("Auto").Range("C" & j) = "BI Limits"
        j = j + 1
    Loop
    
    'BI Limits
    NB_Temp.Sheets(1).Range("D7:D10").Value = Scrap.Sheets("auto").Range("D" & j + 1 & ":D" & j + 4).Value
    NB_Temp.Sheets(1).Range("D11").Value = Scrap.Sheets("auto").Range("D" & j + 5).Value + Scrap.Sheets("auto").Range("D" & j + 6).Value + Scrap.Sheets("auto").Range("D" & j + 7).Value
    NB_Temp.Sheets(1).Range("D12:D13").Value = Scrap.Sheets("auto").Range("D" & j + 8 & ":D" & j + 9).Value
    NB_Temp.Sheets(1).Range("E7:E10").Value = Scrap.Sheets("auto").Range("F" & j + 1 & ":F" & j + 4).Value
    NB_Temp.Sheets(1).Range("E11").Value = (Scrap.Sheets("auto").Range("E" & j + 5) + Scrap.Sheets("auto").Range("E" & j + 6) + Scrap.Sheets("auto").Range("E" & j + 7)) / (Scrap.Sheets("auto").Range("D" & j + 5).Value + Scrap.Sheets("auto").Range("D" & j + 6).Value + Scrap.Sheets("auto").Range("D" & j + 7).Value)
    NB_Temp.Sheets(1).Range("E12:E13").Value = Scrap.Sheets("auto").Range("F" & j + 8 & ":F" & j + 9).Value
    NB_Temp.Sheets(1).Range("F7:F10").Value = Scrap.Sheets("auto").Range("G" & j + 1 & ":G" & j + 4).Value
    NB_Temp.Sheets(1).Range("F11").Value = (Scrap.Sheets("auto").Range("D" & j + 5) + Scrap.Sheets("auto").Range("D" & j + 6) + Scrap.Sheets("auto").Range("D" & j + 7)) / (Scrap.Sheets("auto").Range("D" & j + 9).Value)
    NB_Temp.Sheets(1).Range("F12:F13").Value = Scrap.Sheets("auto").Range("G" & j + 8 & ":G" & j + 9).Value
    
    NB_Temp.Sheets(1).Range("G7:G10").Value = Scrap.Sheets("auto").Range("H" & j + 1 & ":H" & j + 4).Value
    NB_Temp.Sheets(1).Range("G11").Value = Scrap.Sheets("auto").Range("H" & j + 5).Value + Scrap.Sheets("auto").Range("H" & j + 6).Value + Scrap.Sheets("auto").Range("H" & j + 7).Value
    NB_Temp.Sheets(1).Range("G12:G13").Value = Scrap.Sheets("auto").Range("H" & j + 8 & ":H" & j + 9).Value
    NB_Temp.Sheets(1).Range("H7:H10").Value = Scrap.Sheets("auto").Range("J" & j + 1 & ":J" & j + 4).Value
    [b]NB_Temp.Sheets(1).Range("H11").Value = (Scrap.Sheets("auto").Range("I" & j + 5) + Scrap.Sheets("auto").Range("I" & j + 6) + Scrap.Sheets("auto").Range("I" & j + 7)) / (Scrap.Sheets("auto").Range("H" & j + 5).Value + Scrap.Sheets("auto").Range("H" & j + 6).Value + Scrap.Sheets("auto").Range("H" & j + 7).Value)[/b]

 

[jjmclell]

The problem is likely because the denominator in some of the division you're doing in certain cases is equal to 0...anything divided by zero equals infinity...therefore overflow error. I had the exact same problem with some queries I was building and ended up adding a small number such as .001 to all denominators whereever I was doing division as a rough workaround.

NB_Temp.Sheets(1).Range("E11").Value = (Scrap.Sheets("auto").Range("E" & j + 5) + Scrap.Sheets("auto").Range("E" & j + 6) + Scrap.Sheets("auto").Range("E" & j + 7)) / (Scrap.Sheets("auto").Range("D" & j + 5).Value + Scrap.Sheets("auto").Range("D" & j + 6).Value + Scrap.Sheets("auto").Range("D" & j + 7).Value)

 

[Groves22]

Ah... that is the problem!
Thank You!