Checking Individual Cells In A Range To Contain Certain Text

[Mark Brown]

Hi,

I'm trying to write some code where it'll search each cell within a specified range and for each cell that contains "=MIK" it'll copy and paste each cell as a value. I've come up with the following but I think the .contains part isn't a valid command. Can anyone help? Thanks.

Option Explicit
Dim c as Range

Sheets("Trading Summary").Select

For Each c In Range("F36:M53")
If c.Contains = "=MIK" = True Then c.Copy c.PasteSpecial

Next c

 

[NARAYANK991]

Hi ,

You are right that the .Contains is not valid ; you can instead use the Like operator.

IF c.Value Like "*" & "MIK" & "*" THEN

or

IF c.Value Like "*MIK*" THEN

However , the c.Copy c.PasteSpecial is also not valid ; either the Copy method has the destination on the same line , or the PasteSpecial method is a separate statement as in :

c.Copy
c.PasteSpecial

If at all you wish to have multiple statements in the same line , you need to separate them with a colon , as in :

IF c.Value Like "*MIK*" THEN c.Copy : c.PasteSpecial

The full code would be :

Option Explicit

Sub test()
    Dim c As Range

    Sheets("Trading Summary").Select

    For Each c In Range("F36:M53")
        If c.Value Like "*MIK*" Then c.Copy: c.PasteSpecial
    Next c
    Application.CutCopyMode = False
End Sub

Narayan

 

[Marc L]

Hi,

other way with Find method and Found.Value = Found.Value …
​