How to solve this overflow problems

Question

May I ask why do line 5 causes overflow? i checked the data's ans will be 0 over 0, I guess this might be the problem but i am not sure, is there any solution to make this work?

HKColumn = 2

Do Until Cells(6, HKColumn).Value = 0

    Cells(34, 27).Value = (Cells(6, HKColumn) - Cells(5, HKColumn)) / (Worksheets("TOTAL").Cells(6, HKColumn) - Worksheets("Total").Cells(5, HKColumn))
    Cells(34, 26).Value = "2018 " + Cells(3, HKColumn)
    Cells(34, 17).Value = (Cells(11, HKColumn) - Cells(10, HKColumn)) / (Worksheets("TOTAL").Cells(11, HKColumn) - Worksheets("Total").Cells(10, HKColumn))
    Cells(34, 16).Value = "2018 " + Cells(3, HKColumn)

    Cells(32, 27).Value = (Cells(5, HKColumn) - Cells(4, HKColumn)) / (Worksheets("TOTAL").Cells(5, HKColumn) - Worksheets("Total").Cells(4, HKColumn))
    Cells(32, 26).Value = "2017 " + Cells(3, HKColumn)
    Cells(32, 17).Value = (Cells(10, HKColumn) - Cells(9, HKColumn)) / (Worksheets("TOTAL").Cells(10, HKColumn) - Worksheets("Total").Cells(9, HKColumn))
    Cells(32, 16).Value = "2017 " + Cells(3, HKColumn)

    HKColumn = HKColumn + 1

Loop

how is HKColumn declared? Have you put is as a long just to be careful — QHarr
– QHarr, Commented Mar 12, 2018 at 9:48
I did declared as Long Line 6: Cells(34, 17).Value = (Cells(11, HKColumn) - Cells(10, HKColumn)) / (Worksheets("TOTAL").Cells(11, HKColumn) - Worksheets("Total").Cells(10, HKColumn)) Cells(34, 16).Value = "2018 " + Cells(3, HKColumn) — Jaymie
– Jaymie, Commented Mar 12, 2018 at 9:50
So you have 0/0? In VBA this gives 6 (Overflow) - stackoverflow.com/questions/45485285/… — Vityata
– Vityata, Commented Mar 12, 2018 at 9:51

Vityata · Accepted Answer · 2018-03-12 10:12:51Z

0

If all the values are 0, it would give Overflow, because 0/0 throws overflow error in Excel - Why is 0 divided by 0 throwing an overflow error in VBA?

Two ways to solve the problem:

Really not recommended way, which would force the VBA to ignore every error - Write On Error Resume Next on the top of your code. Then write On Error GoTo 0 at the bottom.
Always make a check whether the divisor is different than 0. This is a standard in programming, as far as dividing by 0 is not allowed. Thus something like this or even make it a separate function:

If (Cells(6, HKColumn) - Worksheets("Total").Cells(5, HKColumn))) <> 0 Then
        Cells(34, 27).Value = (Cells(6, HKColumn) - Cells(5, HKColumn)) / the-non-zero-val
End If

answered Mar 12, 2018 at 10:12

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

Collectives™ on Stack Overflow

How to solve this overflow problems

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related