[cram2013]

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:

Sub Plex5_sort()

x = 9
y = 1

Dim lastchar As String

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
        Range(Cells(x, 1), Cells(x, 6)).Cut
        Sheets("Plex5").Select
        Cells(y, 1).Select
        ActiveSheet.Paste
        Sheets("Raw").Select
        x = x + 1
        y = 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!

[anziga]

Should your counter also add outside of your IF sentence? Just a thought after a quick peek at your code...

[cram2013]

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!!

[GGriggs]

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