VBA - capacity overflow error

Question

I have the following code that is causing a

capacity overflow error type 6

due to the code between '------------------------------------'. I will appreciate your help :)!

I was not able to solve it with other discussion.

Sub Calculate_Mix()
    Dim rngUsernameHeader As Range
    Dim rngHeaders As Range
    Dim rngAddress As Range
    Dim rng_R1 As Range
    Dim rng_delisted As Range
    Dim ws As Worksheet
    Dim value As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rngHeaders = Range("1:1") 'Looks in entire first row.
    Set rngUsernameHeader = rngHeaders.Find(what:="VAl MAT'Mar18", After:=Cells(1, 1))

    rngUsernameHeader.Offset(0, 1).EntireColumn.Insert
    rngUsernameHeader.Offset(0, 1).value = "Delisted"

    rngUsernameHeader.Offset(0, 2).EntireColumn.Insert
    rngUsernameHeader.Offset(0, 2).value = "New Launches"

    rngUsernameHeader.Offset(0, 3).EntireColumn.Insert
    rngUsernameHeader.Offset(0, 3).value = "Price_17"

    rngUsernameHeader.Offset(0, 4).EntireColumn.Insert
    rngUsernameHeader.Offset(0, 4).value = "Price_18"

    For Each Cel In Range("F2", Range("F2").End(xlDown))
    If Cel > 0 And Cel.Offset(0, 1) = 0 Then
            Cel.Offset(0, 2).value = Cel.value
    Else: Cel.Offset(0, 2).value = 0
    End If
    If Cel = 0 And Cel.Offset(0, 1) > 0 Then
            Cel.Offset(0, 3).value = Cel.Offset(0, 1).value
    Else: Cel.Offset(0, 3).value = 0
    End If
    Next Cel

    '------------------------------------'
    For Each Cel In Range("H2", Range("H2").End(xlDown))
    If Cel = 0 And Cel.Offset(0, 1) = 0 Then
        value = Cel.Offset(0, -2).value / Cel.Offset(0, -4).value
        Cel.Offset(0, 2).value = value
    Else: Cel.Offset(0, 2).value = 0
    End If
    Next Cel
   '------------------------------------'

End Sub

Also, does H3 or beyond actually have data in it? Because if not, the code will be processing the entire rowset of Excel, which will most likely cause that error. — Scott Holtzman
– Scott Holtzman, Commented May 30, 2018 at 14:18

Olly · Accepted Answer · 2018-05-30 14:14:43Z

2

You've declared value (bad variable name, by the way) as Long. You'll get an overflow error if you try to assign a decimal value to an integer variable (which may well be happening with this line:

value = Cel.Offset(0, -2).value / Cel.Offset(0, -4).value

Try declaring value as Double

Better still, rename and declare dMyValue as Double

answered May 30, 2018 at 14:14

Olly

7,9021 gold badge23 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

VBA - capacity overflow error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related