#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    Excel hangs at If statement in macro


    This may seem really simple, but it's driving me insane!

    I want to write a macro that cuts and pastes data in Excel. I have alphanumeric data in column A, starting at cell A9 (the data itself ranges from "A01" to "P24"). Ideally, it would cut/paste the data only if that alphanumeric string ends in "21", "22", "23" or "24", until it finds an empty cell.

    Here's the code:

    PHP Code:
    Sub Plex5_sort()

    9
    1

    Dim lastchar 
    As String

    Sheets
    ("Raw").Select
    Do While Cells(x1) <> ""
        
    lastchar Right(Cells(x1), 2)
        If 
    lastchar "21" Or lastchar "22" Or lastchar "23" Or lastchar "24" Then
            Range
    (Cells(x1), Cells(x6)).Cut
            Sheets
    ("Plex5").Select
            Cells
    (y1).Select
            ActiveSheet
    .Paste
            Sheets
    ("Raw").Select
            x 
    1
            y 
    1
        End 
    If
        
    Loop
        
    End Sub 
    My workbook sheets are properly named.

    When I run this, it freezes Excel after a few seconds. I left it running overnight and it never finished. I had to force-close Excel, and when I did, I caught a brief glimpse of an error message: "Method '_Default' of object 'Range' failed". I can't catch what line it was on before the program shuts down. So I tried running it again upon re-start, and after a few seconds (just before it freezes), I break it. It appears to be hanging at line 11, the If statement.

    I'm sure it has something to do with the numbers and string definition but I just can't seem to figure it out. I'm sure it's laughable that I've somehow screwed up some very simple code, so be gentle! I will mention that I'm using VB 7.0.

    I've run other macros today that are much more complicated and are working perfectly, so I'm pretty sure it's not a memory issue.

    Any help or suggestions are welcomed and very much appreciated!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    192
    Rep Power
    328
    Should your counter also add outside of your IF sentence? Just a thought after a quick peek at your code...
    Last edited by anziga; April 5th, 2013 at 04:44 PM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by anziga
    Should your counter also add outside of your IF sentence? Just a thought after a quick peek at your code...
    Nice catch, that fixed it! I knew it had to be something simple, but after staring at it all day I figured it just needed some fresh eyes. Thanks for taking a look! I moved just the x variable counter outside the IF statement, but left the y variable counter inside so that the sheet where the data was being pasted didn't end up with a bunch of blanks.

    Thank you again!!
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    18
    Rep Power
    0

    also more efficient


    There is no need to keep moving sheet and cutting and pasting

    if you use

    With sheets("plex5")
    Sheets("Raw").Select
    Do While Cells(x, 1) <> ""
    lastchar = Right(Cells(x, 1), 2)
    If lastchar = "21" Or lastchar = "22" Or lastchar = "23" Or lastchar = "24" Then
    for z = 1 to 6
    .cells(y,z) = cells(x,z)
    next z
    y = y + 1
    End If
    x = x + 1

    Loop
    end with


    you will find your code goes a zillion times faster as both selecting sheets and cutting and pasting are fairly slow

IMN logo majestic logo threadwatch logo seochat tools logo