Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find Text in cell and copy to cell 7 columns over (Excel 2003)

    I need to find the TEXT "HCE" anywhere in the cells D1000 and copy the found cell contents to a new cell 7 columns to the right of the cell with the text
    then loop through the next cells.
    This is thecode I have so far

    Sub Find_HCE()
    Const StrText = "HCE"
    Dim rng As Range
    Set rng = Range("D1000").Find(What:=StrText, LookIn:=xlValues, LookAt:=xlPart)
    Do While Not rng Is Nothing
    rng.Copy _
    Destination:=Range("K1000").End(xlUp).Offset(0, 7)
    Set rng = Range("D1000").FindNext
    Loop
    End Sub

    Can you help with a correction?

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

    Re: Find Text in cell and copy to cell 7 columns over (Excel 2003)

    D1000 is not a valid range - do you mean D11000 or D21000 perhaps?
    If you want to copy the cell contents to 7 columns to the right of the cell in column D you should copy to rng.Offset(0, 7). If you use column K and then offset 7 columns to the right, you end up in column R. Moreover, Range("K1000").End(xlUp).Offset(0, 7) will be the same cell all the time, so each value will overwrite the previous one.
    With FindNext, you must specify after which cell you want to start otherwise you'll keep on finding the first occurrence.
    And you must test that you haven't looped back to the beginning.

    Here is a working version:
    <code>
    Sub Find_HCE()
    Const StrText = "HCE"
    Dim rng As Range
    Dim strFirst As String
    Set rng = Range("D11000").Find(What:=StrText, LookIn:=xlValues, LookAt:=xlPart)
    If Not rng Is Nothing Then
    strFirst = rng.Address
    Do
    rng.Copy Destination:=rng.Offset(0, 7)
    Set rng = Range("D11000").FindNext(After:=rng)
    Loop Until rng Is Nothing Or rng.Address = strFirst
    End If
    End Sub</code>

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Text in cell and copy to cell 7 columns over (Excel 2003)

    Wow! Thanks Hans. You really are a Magician.
    How about a hypothetical so I can learn something else.
    If instead of Copying the Cell to a new Cell 7 columns over,
    how would the code read if I just wanted put an x in a cell 7 columns to the right of the cell with the found text?

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

    Re: Find Text in cell and copy to cell 7 columns over (Excel 2003)

    Change the line
    <code>
    rng.Copy Destination:=rng.Offset(0, 7)
    </code>
    to
    <code>
    rng.Offset(0, 7) = "x"</code>

  5. #5
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Text in cell and copy to cell 7 columns over (Excel 2003)

    Hans:
    Thanks for the lesson

Posting Permissions

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