Copying Rows of Cells based on condition

Kommerce

New Member
Joined
Jun 25, 2014
Messages
27
Hi Guys,

So i've managed to construct some code that allows me to copy specific rows from a sheet that contain the term "Completed" in one of the columns.

Now what I'm trying to do, is add more conditions onto this i.e. (Or) conditions or even perhaps a does not equal "Completed" condition.
Code:
Private Sub btnSLAInFlight_Click()




varSLAStart = DateValue(InputBox("Start Date?"))
varSLAEnd = DateValue(InputBox("End Date?"))


Application.ScreenUpdating = False


'Clear ranges
Worksheets("_Inflight SLA").Range("A:U").Clear


Worksheets("_Combined").Range("A1").EntireRow.Copy
Application.Goto Worksheets("_Inflight SLA").Range("A1"), False
ActiveCell.PasteSpecial (xlPasteValues)


Application.Goto Worksheets("_Combined").Range("F2"), False


Do Until IsEmpty(ActiveCell.Value)
[B]    If ActiveCell.Value = "Completed" Then[/B]
  
        If Worksheets("_Combined").Range("L" & ActiveCell.Row).Value = "Receive" Then
            If DateValue(Worksheets("_Combined").Range("H" & ActiveCell.Row)) >= varSLAStart Then
                If DateValue(Worksheets("_Combined").Range("H" & ActiveCell.Row)) <= varSLAEnd Then
        varCurrRow = ActiveCell.Row
        Worksheets("_Combined").Range("A" & ActiveCell.Row & ":U" & ActiveCell.Row).Copy
        varDestRow = Worksheets("_Inflight SLA").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Application.Goto Worksheets("_Inflight SLA").Range("A" & varDestRow), False
        ActiveCell.PasteSpecial (xlPasteValues)
        ActiveCell.PasteSpecial (xlPasteFormats)
        Application.Goto Worksheets("_Combined").Range("F" & varCurrRow), False
    End If
    End If
    End If
    End If
    ActiveCell.Offset(1, 0).Activate
Loop


End Sub

This above code seems to work no problem however, when I start to modify it to do this:

Code:
Private Sub btnSLAInFlight_Click()




varSLAStart = DateValue(InputBox("Start Date?"))
varSLAEnd = DateValue(InputBox("End Date?"))


Application.ScreenUpdating = False


'Clear ranges
Worksheets("_Inflight SLA").Range("A:U").Clear


Worksheets("_Combined").Range("A1").EntireRow.Copy
Application.Goto Worksheets("_Inflight SLA").Range("A1"), False
ActiveCell.PasteSpecial (xlPasteValues)


Application.Goto Worksheets("_Combined").Range("F2"), False


Do Until IsEmpty(ActiveCell.Value)
[B]    If ActiveCell.Value <> "Completed" Then[/B]
  
        If Worksheets("_Combined").Range("L" & ActiveCell.Row).Value = "Receive" Then
            If DateValue(Worksheets("_Combined").Range("H" & ActiveCell.Row)) >= varSLAStart Then
                If DateValue(Worksheets("_Combined").Range("H" & ActiveCell.Row)) <= varSLAEnd Then
        varCurrRow = ActiveCell.Row
        Worksheets("_Combined").Range("A" & ActiveCell.Row & ":U" & ActiveCell.Row).Copy
        varDestRow = Worksheets("_Inflight SLA").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Application.Goto Worksheets("_Inflight SLA").Range("A" & varDestRow), False
        ActiveCell.PasteSpecial (xlPasteValues)
        ActiveCell.PasteSpecial (xlPasteFormats)
        Application.Goto Worksheets("_Combined").Range("F" & varCurrRow), False
    End If
    End If
    End If
    End If
    ActiveCell.Offset(1, 0).Activate
Loop


End Sub

I get a run time error '13' Type mis match. This is the same as if I didn't have the <> function and rather Or functions including what criteria I want.


Any help would be greatly appreciated as this is I'm assuming a very easy fix that I just can't seem to work out!


Thanks in advance!
 
Last edited:

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,008
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top