Macro that searches a cell for a word

**hgufrin** · 12-04-2006, 11:56 AM

Does anyone out there have code that will search every cell in column G for the word "Panel" and return the word "Yes" in column H.

Note- what is making this hard for me is that the cell in column G may contain the word "Panel" all by itself or it may be an actual sentence that contains the word "Panel". In either case, I would like the word "Yes" in column H.

Thanks for your help and ideas.

**hgufrin** · 12-04-2006, 11:57 AM

Does anyone out there have code that will search every cell in column G for the word "Panel" and return the word "Yes" in column H.

Note- what is making this hard for me is that the cell in column G may contain the word "Panel" all by itself or it may be an actual sentence that contains the word "Panel". In either case, I would like the word "Yes" in column H.

Thanks for your help and ideas.

**MDubbelboer** · 12-04-2006, 12:02 PM

a formulaic approach would be to put the following formula in cell H1 and copy down.
=if(iserror(find("panel",G1,1)),"No","Yes")

if you need to do it with a macro the find method is what you're looking for.
the example from the help:

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

you'd want to amend this so that you are looping through column G and perform your search in an individual cell in G. If not c is nothing then range.offset(1,0)="Yes", else "No".

hth

**hgufrin** · 12-04-2006, 12:10 PM

hth,

thanks for the reply. although, it is too advanced for me. can you adjust this code to make it work? Currently, this is not working because of the "If Activesheet.Cells(x,7)="PLANT".... the equal sign is messing it up.

I do appreciate your time....

x = 2
Do
ActiveSheet.Cells(x, 7).Select
If ActiveSheet.Cells(x,7) = "PLANT" Then

Selection.Offset(0,1) = "Yes"
End If
Else
End If
x = x + 1
Loop Until Trim(ActiveSheet.Cells(x, 1)) = ""

**T-J** · 12-04-2006, 12:28 PM

The original request -

Originally Posted by hgufrin

Does anyone out there have code that will search every cell in column G for the word "Panel" and return the word "Yes" in column H.

Using the same method as MDubbelboer - our old friends the Find and FindNext methods:

Sub PanelCells()
    Dim rngPanelCell As Range, firstAddress As String
    
    With Worksheets(1).Range("G:G")   'search this range on sheet1
        Set rngPanelCell = .Find(what:="Panel", LookIn:=xlValues, LookAt:=xlPart)   'find panel cell
        If Not rngPanelCell Is Nothing Then
            firstAddress = rngPanelCell.Address
            Do
                rngPanelCell.Offset(, 1).Value = "Yes"
                Set rngPanelCell = .FindNext(rngPanelCell)
            Loop While Not rngPanelCell Is Nothing And rngPanelCell.Address <> firstAddress  'keep looking
        End If
    End With

    Set rngPanelCell = Nothing
End Sub