April 4th, 2013, 05:36 PM
-
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()
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!
April 5th, 2013, 04:42 PM
-
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.
April 5th, 2013, 07:16 PM
-
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!!
April 18th, 2013, 10:50 AM
-
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