Loopy about my loop
Loopy about my loop
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Loopy about my loop

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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


  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Guest

    Default

      

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com