Getting Runtime error '6': Overflow error

Question

Code was working fine up until a couple of days ago but now getting the subject-line error. Help?

Sub CopyRows()
    Dim bottomL As Integer
    Dim x As Integer
        bottomL = Sheets("Pacer").Range("L" & Rows.Count).End(xlUp).Row: x = 1

    Dim c As Range
    For Each c In Sheets("Pacer").Range("A1:L" & bottomL)
        If (c.Value = "AFSB" Or c.Value = "TEIGIP4T" Or c.Value = "EPP") Then
            Intersect(c.Parent.Columns("A:Q"), c.EntireRow).Copy Worksheets("Portfolio").Range("A" & x + 1)
            x = x + 1
        End If
    Next c

End Sub

If (c.Value = "AFSB" Or c.Value = "TEIGIP4T" Or c.Value = "EPP") Then — MCT
– MCT, Commented Nov 8, 2017 at 13:19
You've probably got a cell that appears as #######? Try using: If (c.Value2 = "AFSB" Or c.Value2 = "TEIGIP4T" Or c.Value2 = "EPP") Then — Rory
– Rory, Commented Nov 8, 2017 at 13:20
You are absolutely right about the formatting issue where a few cells appear as #######. Unfortunately, the correction you recommended still produces the Runtime error, but the code works when I manually override the formatting issue. Is the easiest thing to insert code that would allow me to format the data as it is being copied over? — MCT
– MCT, Commented Nov 8, 2017 at 13:27
Are those cells formatted as Date? If so, the correction I suggested has never failed me before - are you sure you changed all the Value to Value2? — Rory
– Rory, Commented Nov 8, 2017 at 13:30

shrivallabha.redij · Accepted Answer · 2017-11-08 13:07:53Z

1

Variable bottomL As Integer

Will give overflow error the moment it exceeds 32,767 rows. Try declaring it as long

bottomL As Long

Edit: The rule applies to X as well as it is incrementing.

edited Nov 8, 2017 at 13:07

answered Nov 8, 2017 at 12:56

shrivallabha.redij

5,9021 gold badge15 silver badges28 bronze badges

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

4 Comments

MCT Over a year ago

Gave that a shot, but still running into the overflow error. Not sure what I am doing wrong here

SJR Over a year ago

What is the value of x? Basically, use Long always instead of Integer.

shrivallabha.redij Over a year ago

@SJR Spot on! x is incrementing. I have edited my response.

MCT Over a year ago

I've declared both bottomL and x As Long, but still getting the same error

Storax · Accepted Answer · 2017-11-08 13:09:25Z

0

Try this

Option Explicit

Sub CopyRows()
    Dim bottomL As Long
    Dim x As Long
        bottomL = Sheets("Pacer").Range("L" & Rows.CountLarge).End(xlUp).Row: x = 1

    Dim c As Range
    For Each c In Sheets("Pacer").Range("A1:L" & bottomL)
        If (c.Value = "AFSB" Or c.Value = "TEIGIP4T" Or c.Value = "EPP") Then
            Intersect(c.Parent.Columns("A:Q"), c.EntireRow).Copy Worksheets("Portfolio").Range("A" & x + 1)
            x = x + 1
        End If
    Next c

End Sub

Reason explained here

answered Nov 8, 2017 at 13:09

Storax

12.7k3 gold badges21 silver badges36 bronze badges

4 Comments

MCT Over a year ago

I think we finally got, thanks for the refresh! Was the issue that I wasn't using CountLarge and/or the Explicit declaration at start? So frustrating it could come down to something that seemingly trivial

Storax Over a year ago

Because you were not using CountLarge you got a run time error. Explicit is not important here just best practise

MCT Over a year ago

Well thanks so much for the help, really appreciate it. This is something I use daily so important to get it right. Muchos gracias!

MCT Over a year ago

One more question, is it possible to augment your code slightly so it only copies values over as to preserve the source formatting in the destination tab?

Collectives™ on Stack Overflow

Getting Runtime error '6': Overflow error

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related