"Runtime Error '6': Overflow" When running this VBA?
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: "Runtime Error '6': Overflow" When running this VBA?

  1. #1
    New Member
    Join Date
    Jul 2012
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question "Runtime Error '6': Overflow" When running this VBA?

     
    Hello,
    alvinwlh sent me this VBA which is the solution to this other thread:
    http://www.mrexcel.com/forum/showthr...=1#post3196643

    The code is as follows:
    Code:
    Sub arrangeA_B()
    Dim last_row As Integer
    Dim my_range As Range
    Dim rag, c As Range
    Dim temp
        
        last_row = Range("B" & Rows.count).End(xlUp).Row
        For x = 1 To last_row
            Set c = Range("A:A").Find(Range("B" & x).Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not c Is Nothing Then
                If c.Row <> x And Range("B" & x).Value <> "" Then
                    If c.Offset(0, 1).Value = Range("B" & x).Value Then
                        R = MsgBox("Duplication " & Range("B" & x).Value, vbOKOnly)
                        Exit Sub
                    End If
                        
                    temp = c.Offset(0, 1).Value
                    Temp2 = c.offset(0,2).value
                    c.Offset(0, 1).Value = Range("B" & x).Value
                    c.Offset(0,2).Value = Range("C" & x).Value
                    Range("B" & x).Value = temp
                    Range("C" & x).Value = temp2
                    x = x - 1
                End If
            Else
                Range("B" & Range("B" & Rows.count).End(xlUp).Row + 1).Value = Range("B" & x).Value
                Range("C" & Range("C" & Rows.count).End(xlUp).Row + 1).Value = Range("C" & x).Value
                Range("B" & x).ClearContents
                Range("C" & x).clearcontents
            End If
        Next
        
    End Sub  


    When I run it I get "Runtime Error '6': Overflow". Is there anyone who might know why this is happening? Thanks!

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Location
    Toronto, Canada
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Runtime Error '6': Overflow" When running this VBA?

    Maybe try changing
    Code:
    Dim last_row As Integer
    to
    Code:
    Dim last_row As Long
    Excel 2007/10
    OS: Window 7

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,900
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Runtime Error '6': Overflow" When running this VBA?

    You may simply need to change this:
    Code:
    Dim last_row As Integer
    
    to this:
    Code:
    Dim last_row As Long

  4. #4
    New Member
    Join Date
    Jul 2012
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Runtime Error '6': Overflow" When running this VBA?

    Thank you! The script runs now, but so far it's been running for about 2hrs on a list of about 10k items and claims to be "Not Responding"... Perhaps it's not working?
    When it was just sorting A&B it usually took under 5 minutes in Excel 2010.

  5. #5
    New Member
    Join Date
    Jul 2012
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Runtime Error '6': Overflow" When running this VBA?

      
    Let it run for almost 24hrs, just seems to infinitely loop.
    Bump for code advice.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com