Copy contents if.......

Metrazal · #1 (**permalink**) 02-16-06, 14:55

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

shades · #2 (**permalink**) 02-16-06, 17:52

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.

Fazza · #3 (**permalink**) 02-16-06, 18:35

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

Metrazal · #4 (**permalink**) 02-17-06, 10:09

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

Fazza · #5 (**permalink**) 02-18-06, 17:51

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

Internet Services

Web Development

Digital Marketing

Consumer Tech