Help with VBA coding please :)

[jedibrown]

Good morning,

I wondered if somebody could help me.

I have the following code but I need to work out how to put each variable on another line - I can't figure it out. So each "or/and" combination I want on a seperate line.

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
Sheets("Sheet2").UsedRange.ClearContents
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Not IsError(Range("A" & i)) Then
If LCase(Range("C" & i).Value) <> "complete" And _
LCase(Range("C" & i).Value) <> "rejected-nonissue" And _
(InStr(LCase(Range("F" & i).Value), "compensation payment") > 0 And InStr(LCase(Range("M" & i).Value), "kh") > 0 Or InStr(LCase(Range("F" & i).Value), "compensation payment") > 0 And InStr(LCase(Range("M" & i).Value), "gd") > 0 Or InStr(LCase(Range("F" & i).Value), "compensation payment") > 0 And InStr(LCase(Range("M" & i).Value), "tb") > 0 Or InStr(LCase(Range("F" & i).Value), "compensation payment") > 0 And InStr(LCase(Range("M" & i).Value), "kc") > 0 Or InStr(LCase(Range("F" & i).Value), "compensation payment") > 0 And InStr(LCase(Range("M" & i).Value), "tg") > 0 Or InStr(LCase(Range("F" & i).Value), "compensation payment") > 0 And InStr(LCase(Range("M" & i).Value), "gr") > 0 Or InStr(LCase(Range("F" & i).Value), "compensation payment") > 0 And InStr(LCase(Range("M" & i).Value), "js") > 0 Or InStr(LCase(Range("F" & i).Value), "compensation payment") > 0 And InStr(LCase(Range("M" & i).Value), "sc") > 0) _
Then Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

 

[theBardd]

Good point, you probably want the tests on raw data as well

Private Sub CommandButton1_Click()Dim LR As Long, i As Long

    Sheets("Sheet2").UsedRange.ClearContents
    
    With Worksheets("raw data")
    
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To LR
        
            If Not IsError(.Range("A" & i)) Then
            
                If LCase(.Range("C" & i).Value) <> "complete" And _
                    LCase(.Range("C" & i).Value) <> "rejected-nonissue" And _
                    Year(.Range("K" & i).Value) = 2012 And Month(.Range("K" & i).Value) = 1 And _
                    InStr(LCase(.Range("F" & i).Value), "compensation payment") > 0 And _
                    (InStr(LCase(.Range("M" & i).Value), "kh") > 0 Or _
                    InStr(LCase(.Range("M" & i).Value), "gd") > 0 Or _
                    InStr(LCase(.Range("M" & i).Value), "tb") > 0 Or _
                    InStr(LCase(.Range("M" & i).Value), "kc") > 0 Or _
                    InStr(LCase(.Range("M" & i).Value), "tg") > 0 Or _
                    InStr(LCase(.Range("M" & i).Value), "gr") > 0 Or _
                    InStr(LCase(.Range("M" & i).Value), "js") > 0 Or _
                    InStr(LCase(.Range("M" & i).Value), "sc") > 0) Then
                    
                    .Rows(i).Copy Destination:=Worksheets("Sheet2").Range("A" & .Rows.Count).End(xlUp).Offset(1)
                End If
            End If
        Next i
    End With
End Sub

 

[jedibrown]

Thanks - unfortunately it's not pulling anything though. It seems like it's working but, nothing is pasted into sheet 2 (the active sheet).

 

[theBardd]

That suggests nothing matches all of the criteria. Without seeing the data it is hard to be more explicit.

 

[jedibrown]

Hi - don't worry - must have been user error as it's fine now!

Thanks again for your help!