CODEWITHSUNDEEP
vbaexcel
Seidhe
Seidhe

Reputation: 83

"Overflow" Error (6) when doing calculations on arrays

I am creating a macro in VBA which finds values in one sheet and uses them to populate array MyArr(11,4) and then does calculation. Find and populating part works great, problem is the latter part.

.Range("C2").Value = MyArr(11, 4) / MyArr(11, 1)
.Range("D2").Value = (MyArr(9, 4) + MyArr(10, 4) + MyArr(11, 4)) / (MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1))
.Range("E2").Value = (MyArr(0, 4) + MyArr(1, 4) + MyArr(2, 4) + MyArr(3, 4) + MyArr(4, 4) + MyArr(5, 4) + MyArr(6, 4) + MyArr(7, 4) + MyArr(8, 4) + MyArr(9, 4) + MyArr(10, 4) + MyArr(11, 4)) / (MyArr(0, 1) + MyArr(1, 1) + MyArr(2, 1) + MyArr(3, 1) + MyArr(4, 1) + MyArr(5, 1) + MyArr(6, 1) + MyArr(7, 1) + MyArr(8, 1) + MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1))

This is the fragment of code in question. It causes Overflow error (6). I know this error happens due to one of the values in array being empty. Is there an easy way to stop using empty values but still execute the code for the filled ones? So if for example MyArr(9,4) is empty it would still execute the 2nd line of code because there are values in (10, 4), (11, 4) with omitting (9,4).

If the rest of the code is needed please inform me.

Upvotes: 0

Views: 259

Answers (1)

braX
braX

Reputation: 11755

As @Jon mentioned, check for division by zero before doing the division:

If MyArr(11, 1) <> 0 Then 
  .Range("C2").Value = MyArr(11, 4) / MyArr(11, 1)
Else
  .Range("C2").Value = 0
End If

If (MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1)) <> 0 Then
  .Range("D2").Value = (MyArr(9, 4) + MyArr(10, 4) + MyArr(11, 4)) / (MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1))
Else
  .Range("D2").Value = 0
End If

If (MyArr(0, 1) + MyArr(1, 1) + MyArr(2, 1) + MyArr(3, 1) + MyArr(4, 1) + MyArr(5, 1) + MyArr(6, 1) + MyArr(7, 1) + MyArr(8, 1) + MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1)) <> 0 Then
  .Range("E2").Value = (MyArr(0, 4) + MyArr(1, 4) + MyArr(2, 4) + MyArr(3, 4) + MyArr(4, 4) + MyArr(5, 4) + MyArr(6, 4) + MyArr(7, 4) + MyArr(8, 4) + MyArr(9, 4) + MyArr(10, 4) + MyArr(11, 4)) / (MyArr(0, 1) + MyArr(1, 1) + MyArr(2, 1) + MyArr(3, 1) + MyArr(4, 1) + MyArr(5, 1) + MyArr(6, 1) + MyArr(7, 1) + MyArr(8, 1) + MyArr(9, 1) + MyArr(10, 1) + MyArr(11, 1))
Else
  .Range("E2").Value = 0
End If

Upvotes: 3

Related Questions