vba change cell content

[ottasight]

Hi, i am trying to find certain strings in a column list that contains construction tasks. once found, the cell is rewritten with only the search string. for example, variable X is "dig footers". the cell contains, "start foundation, dig footers, (po435) thursday". i need the cell to only contain "dig footers". i'll be putting this in a loop to search the whole column, but what i have so far isn't working. thanks for any help

Sub Changecontent() Dim xFind As String DIM XREP AS STRING XREP = "dig footers" XFIND=ACTIVECELL ACTIVECELL.SELECT ACTIVECELL.Replace What:="*XFIND*", Replacement:=XREP, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub

 

[Joe4]

No need for any loop. Replace can do a whole range at once.

Here is some code that will do the whole selected area at once. You can set "rng" equal to any range (i.e. a whole column):

Sub Changecontent()

    Dim XREP As String
    Dim rng As Range
   
'   Set range to search/replace
    Set rng = Selection

'   Designate value to find/replace
    XREP = "dig footers"

'   Do replacement on whole range at once
    rng.Replace What:="*" & XREP & "*", Replacement:=XREP, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
       
End Sub