Expected Do before loop (compile error)

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:

Private Sub CommandButton2_Click()

'Sort by Becky States

'

'

Dim x As String

Dim found As Boolean

 

Range("D2").Select

x = "SC or AL Or AR Or MS Or AZ Or TN Or GA Or TX Or IL"

found = False

 

 

Do Until IsEmpty(ActiveCell)

If ActiveCell.Value = x Then

found = True

Exit Do

ActiveCell.Offset(1, 0).Select

If found = True Then

Range(ActiveCell & ":" & ActiveCell.Offset(0, -4)).Select

Selection.Cut

Sheets("sheet3").Activate

Sheets("sheet3").Range(Rows.Count).End(xlUp).Offset(1).Select

Selection.PasteSpecial xlPasteValues

Else: If ActiveCell.Value <> x Then found = False

 

End If

Loop

 

End Sub

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"

If ActiveCell.Value = x Then

Maybe you can try this:

Do Until IsEmpty(ActiveCell)
    
    Select Case ActiveCell.Value
        Case "SC", "AL", "AR", "MS", "AZ", "TN", "GA", "TX", "IL"
            ActiveCell.Offset(0, -4).Cut
            Sheets("sheet3").Range("A" & rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End Select
    
    ActiveCell.Offset(1, 0).Select
Loop

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.

Private Sub CommandButton2_Click()

'Sort by Becky States

'

'

Dim x As String

Dim found As Boolean

 

Range("D2").Select

x = "SC or AL Or AR Or MS Or AZ Or TN Or GA Or TX Or IL"

found = False

 

 

Do Until IsEmpty(ActiveCell)

   

    Select Case ActiveCell.Value

        Case "SC", "AL", "AR", "MS", "AZ", "TN", "GA", "TX", "IL"

            ActiveCell.Offset(0, -3).Select

            Selection.Cut

            Sheets("sheet3").Activate

            Sheets("sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).Select

            ActiveCell.PasteSpecial Paste:=xlPasteValues

      

    End Select

   

    ActiveCell.Offset(1, 0).Select

Loop

 

 

End Sub

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:

Do Until IsEmpty(ActiveCell)
    
    Select Case ActiveCell.Value
        Case "SC", "AL", "AR", "MS", "AZ", "TN", "GA", "TX", "IL"
            Range(ActiveCell.Offset(0, -4).address & ":" & ActiveCell.address).Copy Sheets("sheet3").Range("A" & rows.Count).End(xlUp).Offset(1)
            ActiveCell.EntireRow.Delete Shift:=xlUp
    End Select
    
    ActiveCell.Offset(1, 0).Select
Loop

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

Private Sub CommandButton1_Click()

'Sort by Becky States

'

'

Sheets("Sheet2").Range("D2").Select

Do Until IsEmpty(ActiveCell)

    

    Select Case ActiveCell.Value

        Case "SC", "AL", "AR", "MS", "AZ", "TN", "GA", "TX", "IL"

            Range(ActiveCell.Offset(0, -3).Address & ":" & ActiveCell.Address).Copy Sheets("sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)

            ActiveCell.EntireRow.Delete Shift:=xlUp

    End Select

    

    ActiveCell.Offset(1, 0).Select

Loop

 

 

End Sub

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!