CODEWITHSUNDEEP
excelruntime-errorvba
user1671860
user1671860

Reputation: 75

Run-time error 6: Overflow: Excel VBA

I have been trying to resolve this but could not. I have the following VBA code. While running it gives "run time error 6: Overflow". I guess I have assigned a right type to each variable. Please help!!

Sub UseCoeff()
Dim a, b As Long
Dim Value1 As Double

ThisWorkbook.Sheets("UseTableBEA").Activate
For b = 2 To 427
    For a = 2 To 431

        Value1 = ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value / ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value
        ThisWorkbook.Sheets("UseCoeff").Cells(a, b).Value = Value1

    Next a
Next b
End Sub

Each cell will have a result that may be between 0 and 1 up to eight decimal places. Thanks in advance!!

Upvotes: 3

Views: 46721

Answers (2)

Daniel
Daniel

Reputation: 13122

If ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value is ever 0, then ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value / ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value will cause the Overflow error if ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value is also 0.

To avoid this, use an if statement to filter out the 0s, just update the comment with the actual logic you want:

If ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value = 0 Then
    Value1 = 'Whatever you want but probably 0.
Else
    Value1 = ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value / ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value
End If

Upvotes: 3

Alistair Weir
Alistair Weir

Reputation: 1849

Dim a, b As Long

a is not declared as a long it is declared as a variant try this:

Dim a as long, b as Long

Edit: Having tested this, it still produces a Stack Overflow error. However it is good practice to declare your variables properly.

You also don't need this line:

ThisWorkbook.Sheets("Sheet1").Activate

and if you declare UseTableBEA you can slim down your code:

Dim Ws1 as Worksheet
Set Ws1 = Sheets("UseTableBEA")

You can then use Ws1 where you have previously used ThisWorkbook.Sheets("UseTableBEA")

Upvotes: 2

Related Questions