Overflow error in VBA calculation (division)

Question

In the macro below I get an Overflow error when calculating Rate = cell.Offset(0, 7).Value / cell.Offset(0, 2).Value - regardless of whether Rate is declared as Long or Double. Kindly help to identify the error.

Sub Apply_ProRata()
Dim DistVal As Integer
Dim Rate As Long
Dim ID As String
Dim cell As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Test")

For Each cell In ws.Range("B9:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
    If Len(cell.Value) = 20 Then
        ID = cell.Value
        
        DistVal = cell.Offset(0, 2).Value - cell.Offset(0, 7).Value
        Rate = cell.Offset(0, 7).Value / cell.Offset(0, 2).Value
        
        cell.Offset(0, 13).Value = DistVal
        cell.Offset(0, 14).Value = Format(Rate, "00.0%")
        
        For Each cell2 In ws.Range("B9:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
            If Len(cell2.Value) > 20 And InStr(cell2.Value, ID) = 1 Then
                cell2.Offset(0, 15).Value = cell2.Value / (DistVal * Rate)
            End If
        Next cell2
    End If
Next cell 
End Sub

In natural language the macro is to do:

For each cell in column B where the length of the cell value = 20 characters

Set ID = value in cell in column B

Set value in cell O equal to value in cell D minus value in cell I

Set value in cell P equal to value in cell I divided by value in cell D number formatted as percentage "00.0%"

Set DistVal = value in cell O

Set Rate = value in cell P

For each row in column B if the the length of the cell value > 20 characters and the InStr cell value starts with the ID string

Set value in cell Q equal to the value of cell D divided by (DistVal * Rate)

Next row

Does it work if you change it to: Rate = cell.Offset(0, 7).Value2 / cell.Offset(0, 2).Value2? If so, do you have any cells on the sheet appearing as #####? — Rory
– Rory, Commented Sep 24, 2024 at 8:57
What are the values of cell.Offset(0, 7) and cell.Offset(0, 2) when this error happens? — FunThomas
– FunThomas, Commented Sep 24, 2024 at 9:20

Michal · Accepted Answer · 2024-09-24 09:22:14Z

2

Declaring Rate and DistVal as Double should do the trick.

Sub Apply_ProRata()
    Dim DistVal As Double
    Dim Rate As Double
    Dim ID As String
    Dim cell As Range
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Test")
    
    On Error GoTo ErrorHandler ' Add error handling

    For Each cell In ws.Range("B9:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
        If Len(cell.Value) = 20 Then
            ID = cell.Value
            
            If cell.Offset(0, 2).Value <> 0 Then ' Ensure no division by zero
                DistVal = cell.Offset(0, 2).Value - cell.Offset(0, 7).Value
                Rate = cell.Offset(0, 7).Value / cell.Offset(0, 2).Value
                
                cell.Offset(0, 13).Value = DistVal
                cell.Offset(0, 14).Value = Format(Rate, "00.0%")
                
                For Each cell2 In ws.Range("B9:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
                    If Len(cell2.Value) > 20 And InStr(cell2.Value, ID) = 1 Then
                        cell2.Offset(0, 15).Value = cell2.Value / (DistVal * Rate)
                    End If
                Next cell2
            Else
                cell.Offset(0, 14).Value = "Div/0 Error"
            End If
        End If
    Next cell
    
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description
End Sub

answered Sep 24, 2024 at 9:22

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cdfj Over a year ago

Thanks Michal - it was the addition of If cell.Offset(0, 2).Value <> 0 that solved the problem! This also needs to be added for cell2: If Len(cell2.Value) > 20 And InStr(cell2.Value, ID) And cell.Offset(0, 2).Value <> 0

Collectives™ on Stack Overflow

Overflow error in VBA calculation (division)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related