Loopy about my loop

keithkemble

Board Regular
Joined
Feb 15, 2002
Messages
160
I posted this Sat am and am now lost.
Can someone help me with theis routine.
It worked perfectly well. i.e. it scroll line by line to the bottom of the range and copies the selected range to another sheet and pastes incrementally.
Brill !
However I only want it to select the cell with a "c" or "C" in column E.

I cannot get it to select only those rows, Any suggestions.

Thanks :-
Sub cmr()
Dim i
i = 15
' To select condition c or 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
' End If
Next
Sheets("mobile").Select
Range("E15").Select
End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Try changing your code to:

Sub cmr()
Dim i
i = 15
' To select condition c or 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 UCase(ActiveCell.Value) = "C" Then
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
End If
i = Selection.Row + 1
End If
Next
Sheets("mobile").Select
Range("E15").Select
End Sub
 
Upvote 0
Barry, Thanks,
I tried your suggestion but the same thing happened as my previous attemps, That is it only selects the one row and then stops.
kk
 
Upvote 0
Oops, try this (slightly modified your code) I think it will work for you.

Sub cmr()
Dim i
Dim LastRow As Long
LastRow = Range("E15").End(xlDown).Row
i = 15
' To select condition c or C in column e then place in sheet cmr at A15
Sheets("mobile").Select
Range("e15").Select
Do Until i > LastRow
If ActiveCell.Value = "" Then Exit Do
If UCase(ActiveCell.Value) = "C" Then
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
End If
i = Selection.Row + 1
End If
Loop
Sheets("mobile").Select
Range("E15").Select
End Sub
 
Upvote 0
Thanks Barrie
While you were updating the board I cracked it.

I have used your line UCase etc but added the following after
i = Selection + 1

ElseIf UCase (activecell.value) <> "C" then
i = Selection + 1
Range ("E" & i).select

This forces the active cell to drop one row and then re evaluate.

Thank for you help
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top