0

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
Jaymie
  • 1
  • 1
  • how is HKColumn declared? Have you put is as a long just to be careful – QHarr Mar 12 '18 at 09:48
  • 4
    Which line is line 6? – Vityata Mar 12 '18 at 09:48
  • 1
    @Vityata i was just about to ask that! – QHarr Mar 12 '18 at 09:49
  • 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 Mar 12 '18 at 09:50
  • 1
    So you have `0/0`? In VBA this gives `6` (Overflow) - https://stackoverflow.com/questions/45485285/why-is-0-divided-by-0-throwing-an-overflow-error-in-vba – Vityata Mar 12 '18 at 09:51
  • FYI all of the values are 0 in the cells in this case, but the values will change monthly, does it mean that theres no way i can solve it if all values are 0? – Jaymie Mar 12 '18 at 09:54
  • to append strings use `&` instead of `+` Because `Debug.Print "2017 " + 5` it will start calculating even if 2017 is a string! – Pᴇʜ Mar 12 '18 at 09:55
  • You can always make a check whether the divisor is a not a `0` and continue with the calculation then. Or the easiest & dirtiest solution - Write `On Error Resume Next` before `HKColumn = 2` and it would "work". – Vityata Mar 12 '18 at 10:00

1 Answers1

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
Vityata
  • 42,633
  • 8
  • 55
  • 100