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]

Is this what you mean?

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("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:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
        End If
    Next i
End Sub

 

[jedibrown]

Perfect - simple as that! Thank you very much.

I don't suppose you know much about dates and VBA do you? I wanted to add another variable so that it only pulls through lines with a date between 1st January 2012 and 31st January 2012 from column K

Thanks again for your help - much appreciated.

 

[theBardd]

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 _
                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:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
        End If
    Next i
End Sub

 

[jedibrown]

Thanks for that - unfortunately I am getting a run time error 13 - type mismatch?

Any ideas?

Thanks

 

[theBardd]

That suggests that you have text in one of the date cells. The most likely candidate is the first row, which is probably a header. Change the loop to go from 2 To LR rather than 1 To LR.

 

[jedibrown]

Brilliant - works perfectly. Thank you very, very much!!

 

[jedibrown]

Apologies - another question if that would be OK please?

I want this to pull the data from a sheet called "raw data" as opposed to the sheet the button is in. Do you know how I work write this?

Many thanks again

 

[theBardd]

Worksheets("raw data").
Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)​

That is all one line, the forum is throwing the line.

 

[jedibrown]

Sorry - you may have realised I'm quite new at this.

I thought I would have to put coding at the start to change the range for the "raw data" sheet rather than the active sheet that the button is in?

Thanks - sorry!