Loopy over a loop that works (please help)

Posted by keith on February 16, 2002 4:18 AM

I have a loop that copies a range from one sheet to another until the bottom of the range.

All well and good, but I want it to only pick the row if the cell value is = to "c" or "C", ignore every thing else.

I can't see the wood for the trees
can you help?

as it stand now the rooutine will copy and paste all. The line where I am having trouble is the active cell line.

THANKS

Sub cmr()
Dim i
i = 15
' To select condition c in column e then place in
' sheet cmr at A15
Sheets("mobile").Select
Range("e15").Select
For Each X In Range("E15", Range("E16").End(xlDown))
If X = "" Then Exit For
' If ActiveCell.Value &LT;&GT; "c" Then GoTo nextrow
Sheets("mobile").Activate
Range("A" & i).Select
Range("A" & i & ":H" & i).Select
Selection.Copy
Sheets("cmr").Select
Range("A15").Select
Sheets("cmr").Select
Range("A2000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
Sheets("mobile").Select
nextrow: i = Selection.Row + 1
' End If
Next
Sheets("mobile").Select
Range("E15").Select
End Sub

Posted by Bariloche on February 16, 2002 4:54 AM

keith,

Here's how I rewrote your code. Compare it line for line with yours and see if you can understand why I changed yours.

Sub cmr()
    Dim i As Integer
' To select condition c in column e then place in
' sheet cmr at A15
    Sheets("mobile").Select
    Range("E15").Select
    For Each cell In Range("E15", Range("E16").End(xlDown))
        i = cell.Row
        If cell.Value = "" Then Exit For
            If UCase(cell.Value) = "C" Then
                Sheets("mobile").Range("A" & i & ":H" & i).Copy Destination:=Sheets("cmr").Range("A2000").End(xlUp).Offset(1, 0)
            End If
    Next cell
End Sub

I realize that this might not be exactly what you need, but look through it and see if it helps.

enjoy

Posted by Artemidorus on February 16, 2002 7:42 AM

If you only need to paste the values to Sheet "cmr", the line that reads :-

Sheets("mobile").Range("A" & i & ":H" & i).Copy Destination:=Sheets("cmr").Range("A2000").End(xlUp).Offset(1, 0)

should be changed to :-

Sheets("cmr").Range("A2000").End(xlUp).Offset(1, 0).Resize(,8)=Sheets("mobile").Range("A" & i & ":H" & i).Value