Expected Do before loop (compile error)

[Rokn]

I am wanting the code below to find data in D2 and if it returns any of the states abbreviated in the variable "x," to grab the range of the active cell and 4 columns to the left of it (A & Value of row), cut it, and paste it onto the next first empty row on sheet 3. I'm trying to learn looping so that it will continue down column D until there's an empty cell. When I run the code as is, it gives me a compile error. I'm really new at the Visual Basic end of excel, but I love what I've seen and figuring out why this is failing will help me tremendously.

I've noticed I don't have an else statement to tell it what to do if it isn't one of the values defined by "x," figure that might be part of the problem . . . and it keeps asking me for a Do before Loop. I looked at the syntax and played with it for a while, still struggling. Any help or insights you can provide are greatly appreciated:

Please Login or Register  to view this content.

[millz]

First, the If statement isn't that clever to interpret that you mean to check for SC or AL or AR, etc.

What this code does is really checking whether the active cell has this entire string content -> "SC or AL Or AR Or MS Or AZ Or TN Or GA Or TX Or IL"

Please Login or Register  to view this content.

Maybe you can try this:

Please Login or Register  to view this content.

[Rokn]

I tried that string of code with a couple of modifications (see below), and it gave me a paste range error (runtime error 1004, paste special method of range class failed) . . . I tried a couple of different things, including activating sheet 3, and redefining range, etc., but could not get it to work. I have a work around that involves generating SQL code in another database storage program and then exporting the code to SAS (another SQL) program. But, SQL and the programs I'll be using are WAY more foreign to me and, although they remove some limitations, they make me nervous b/c my big bosses NEED the data to be correct (obviously) and I really don't trust myself considering my own personal limitations.

Here's the code I tried, see if you think there's an easy fix. Either way, I appreciate you helping. I had a response to this string quite a bit sooner than I had suspected I would. With the boolean, I think I might just need an else / false statement (if returned result does not match the desired criteria then move to the next cell). I don't know, the more I think about it, the more lost I get. I didn't know if I needed the stuff I already had in the original equation to appear above your code, or if I would be able to omit it.

Please Login or Register  to view this content.

[millz]

If it's giving a paste error, maybe you need to tell us more what are you trying to cut and paste. I re-read your first post and it seems you wanted 4 columns of data including that active cell, initially I thought you only wanted that cell that is 4 columns to the left of the active cell.

Try this:

Please Login or Register  to view this content.

[HaHoBe]

Hi, Rokn,

maybe give this code a try (when deleting rows I start at the bottom an work up towards the top line):

Please Login or Register  to view this content.

@millz:
being in Column D an Offset of 4 columns should raise an error.

Ciao,
Holger

[Rokn]

So, this one goes to Millz, although the code needed slight amending:

Please Login or Register  to view this content.

This one is solved. You cats just saved me some serious time at work trying to figure this one out. Thanks again for all the assistance!