Changing a macro to not delete some lines

[sncb]

Hi All..Im new to this forum and needing some assistance.

I have this section of a macro that has conditional changes such that it deletes a few lines if the 6th column is a no..Instead I want it to copy those lines and paste them into another worksheet...Below is the original code..


Application.CutCopyMode = False
For intCounter = 2 To intNoLinesFollowUp
If Cells(intCounter, 6).Value = "No" Then
Rows(intCounter).Select
Selection.Delete Shift:=xlUp
intCounter = intCounter - 1
End If

 

[sncb]

Yes..column B which is the transit number is the unique identifier for each row.

 

[VoG]

Try

Lastdate = Sheets("Test").Range("G" & Rows.Count).End(xlUp).Value
For intCounter = 2 To IntNoLinesFollowUp
If Cells(intCounter, 6).Value = "No" Then
    If IsError(Application.Match(Cells(intCounter, 2), Sheets("Test").Columns("B"), 0)) Then
        Rows(intCounter).Cut Destination:=Sheets("Test").Range("A" & Rows.Count).End(xlUp).Offset(1)
        Sheets("Test").Range("G" & Rows.Count).End(xlUp).Offset(1).Value = Format(Date, "dd/mm/yyyy")
        Sheets("Test").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Date - Lastdate
    End If
End If
Next intCounter
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[sncb]

When I entered this line code, I got a compile error:

"Next without for"

and the cursor went to the 3rd last line of the code

 

[VoG]

That code compiles for me. Please post your entire code.

 

[sncb]

Also for this line,

Sheets("Hold").Range("S" & Rows.Count).End(xlUp).Offset(1).Value = Date - Lastdate

I got error:

Runtime error 13

Type mismatch

 

[sncb]

Here it is The full code



Lastdate = Sheets("Test").Range("G" & Rows.Count).End(xlUp).Value
intNoLinesFollowUp = 16
For intCounter = 2 To intNoLinesFollowUp
If Cells(intCounter, 6).Value = "No" Then
Rows(intCounter).Cut Destination:=Sheets("Test").Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheets("Test").Range("G" & Rows.Count).End(xlUp).Offset(1).Value = Format(Date, "dd/mm/yyyy")
Sheets("Test").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Date - Lastdate
End If
Next intCounter

On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Range("A1").Select

 

[VoG]

Try the code that I posted here http://www.mrexcel.com/forum/showpost.php?p=2654023&postcount=22

 

[sncb]

It did run but in column H it does not show no of days since ran. Im assuming since its run today probably will see '1' tomorrow in col H when I run it tomorrow.

Other than that worked liked a charm. Will report back tomorrow.

Thanks once again for ure unconditional assistance. U're great.

 

[VoG]

It did run but in column H it does not show no of days since ran. Im assuming since its run today probably will see '1' tomorrow in col H when I run it tomorrow.

Yes, presumably it entered 0 in column H, Tomorrow it should enter 1.

 

[sncb]

actually no..there's no '0's just a blank column H but will report back tomorrow