Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    14

    Unanswered: Copy contents if.......

    I am looking for a way to do the following:

    If ("d1:d999") = "text in the cell" then copy ("e1:e999") to ("f1:f999")

    In other words I have a standard text like "tax" in d3, d18, d44, etc. If this text exsists then I want to copy e3, e18, e44, etc. to f3, f18, f44.

    If this possible?

    Thanks,

    Met

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. Try this:

    Code:
    Sub test()
        Dim myRng As Range
        Dim Cel As Range
        Set myRng = Range("D2:D999")
        For Each Cel In myRng
            If Cel.Value = "tax" Then
                Cel.Copy Cel.Offset(0, 1)
                Cel.Copy Cel.Offset(0, 2)
            End If
        Next Cel
    End Sub
    Change range accordingly.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2006
    Posts
    113
    A slight variation to improve speed using the specification that you will be looking for a text value in column "D".

    Shades' code modified to initially select a limited range - only cells that contain text, using SpecialCells.

    regards,
    Fazza

    Code:
    Sub test()
    
        Dim myRng As Range
        Dim Cel As Range
        
        On Error Resume Next
        Set myRng = Range("D2:D999").SpecialCells(xlCellTypeConstants, xlTextValues)
    
        For Each Cel In myRng
           With Cel
                If .Value = "tax" Then .Offset(, 1).Copy .Offset(, 2)
            End With
        Next Cel
    
    End Sub

  4. #4
    Join Date
    Feb 2006
    Posts
    14

    Ok, thanks.. One more question

    Ok, both work good. One more question:

    What is the best way to implement this code on any spreadsheet that I pull up?


    Thanks,

    Met

  5. #5
    Join Date
    Feb 2006
    Posts
    113
    Hi Met,

    The best way will depend on what you want to achieve. Please fully explain how/when you'd like it to operate; then advice can be offered.

    regards,
    Fazza

Posting Permissions

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