MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 <> "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