CODEWITHSUNDEEP
excelvba
t.mo
t.mo

Reputation: 263

What is the overflow error to debug?

I receive an "Overflow" error, but the Sub runs as expected. What is the error I am overlooking? Thanks in advance.

Sub Bill_Detail_Exp_Prem_BUTTON1_()

Dim LastRow As Long
Dim wb1 As Workbook
Dim i As Long

Set wb1 = Workbooks("macro all client v.01.xlsm")

LastRow = wb1.Sheets("Detail").range("C:C").Find("", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For i = 7 To LastRow


    If wb1.Sheets("Detail").Cells(i, 15) <> 0 And wb1.Sheets("Detail").Cells(i, 16) = 0 Then


        wb1.Sheets("Detail").Cells(i, 1) = ((wb1.Sheets("Detail").Cells(i, 15)))

    Else

        wb1.Sheets("Detail").Cells(i, 1) = ((wb1.Sheets("Detail").Cells(i, 17)) * (wb1.Sheets("Detail").Cells(i, 15))) / (wb1.Sheets("Detail").Cells(i, 16))

    End If

Next i


End Sub

Upvotes: 0

Views: 1912

Answers (3)

Shai Rado
Shai Rado

Reputation: 33682

I would modify the IF logics a bit :

For i = 7 To LastRow
    If wb1.Sheets("Detail").Cells(i, 16) = 0 Then
        If wb1.Sheets("Detail").Cells(i, 15) <> 0 Then
            wb1.Sheets("Detail").Cells(i, 1) = ((wb1.Sheets("Detail").Cells(i, 15)))
        End If
    Else ' it means wb1.Sheets("Detail").Cells(i, 16) <> 0 , so no division by 0 and no Overflow Error
        wb1.Sheets("Detail").Cells(i, 1) = ((wb1.Sheets("Detail").Cells(i, 17)) * (wb1.Sheets("Detail").Cells(i, 15))) / (wb1.Sheets("Detail").Cells(i, 16))
    End If
Next i

Upvotes: 1

Darrell H
Darrell H

Reputation: 1886

You have tested for Cells(i,16) not being zero, but only in the case that Cells(i,15)<>0. You may need to add another test, an ElseIf to test for Cells(i,16) being zero before proceeding to the final condition. An overflow is the same as #DIV/0.

Upvotes: 1

tb189
tb189

Reputation: 2012

Difficult to see what the problem is without getting a view of the data, but you can try the following: replace

LastRow = wb1.Sheets("Detail").range("C:C").Find("", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

with

LastRow = wb1.Sheets("Detail").range("C1").End(xlDown).Row + 1

Also note that you can debug VBA by placing breakpoints (F9) in the code.

Upvotes: 0

Related Questions