Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stop loop at specific cell (XL 2003)

    I'm sure this is somewhere on Google or here in the lounge, but my brain has turned to cream cheese.
    I'm trying to move down one column and move the data to another. The field contains something like
    "SUBCOUNT 10". I want to move the data from column A to column D, then delete the text part, leaving only the number. This starts in, say A5.

    Cells.Find(What:="subcount", After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    Selection.Cut
    ActiveCell.Offset(-1, 4).Range("A1").Select (moves to column D)
    ActiveSheet.Paste
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True (removes all but the number value)
    ActiveCell.Offset(1, -4).Range("A1").Select (returns to column A)

    'Loop here

    The code does some other stuff like deleting totally blank rows to keep things neat while it works. Where I'm stuck is, I want it to loop until it gets to, say A2500, but I can't figure it out the syntax or command. If I use a loop with a counter, it can go to another location if the word subcount still exists and mess that up. I can do loops with counters easily, but this one has me beaten. Thanks for any help.
    Jim Whitt
    Pharmacist
    Temple, Texas

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stop loop at specific cell (XL 2003)

    You could use something like

    Do
    your code here
    ...
    ...
    ActiveCell.Offset(1, 0).Select
    Loop Until ActiveCell.Row = 2500

    or

    Do While ActiveCell.Row <= 2500
    your code here
    ...
    ...
    ActiveCell.Offset(1, 0).Select
    Loop

    BTW it would be possible to avoid selecting cells, this is usually more efficient.

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stop loop at specific cell (XL 2003)

    Hans, thanks a lot. I knew it wasn't that complicated. Calcified neurons, I guess. So, the code could like what is below. Are you saying I could leave off this statement:
    'ActiveCell.Offset(1, -4).Range("A1").Select'
    as long as the word subcount is not in any other column?

    <font color=blue> Do While ActiveCell.Row <= 2500 </font color=blue>

    Cells.Find(What:="subcount", After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    Selection.Cut
    ActiveCell.Offset(-1, 4).Range("A1").Select
    ActiveSheet.Paste
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True
    ActiveCell.Offset(1, -4).Range("A1").Select

    <font color=blue>Loop</font color=blue>

    Thanks,
    Jim
    Jim Whitt
    Pharmacist
    Temple, Texas

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stop loop at specific cell (XL 2003)

    No, you should return to the original column, just to make sure.

    BTW, the line

    ActiveCell.Offset(-1, 4).Range("A1").Select

    can be simplified to

    ActiveCell.Offset(-1, 4).Select

  5. #5
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stop loop at specific cell (XL 2003)

    Again, my sincere thanks.

    Jim
    Jim Whitt
    Pharmacist
    Temple, Texas

Posting Permissions

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