worksheet vba does not work

[Jeffreyxx01]

Hi guys,

I tried to make this VBA code using the record and change some thing in,
Now it does not work,

Sub Find()
'
' find Macro
'


'
    Worksheets("PipelineData").Columns("D:D").Select
    Selection.Replace What:="In Progress", Replacement:="Assessment", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Can someone help,
Thanks

 

[Joe4]

Are you on the Pipeline Data sheet when the procedure is called? If so, it will not work.
Also, you need to account for the possibility of it finding no matches (which would call an error).
Lastly, it is never a good idea to use reserved words (names of existing functions, properties, methods, etc) like "Find" as the name of Procedures, Variables, or Custom Functions.
So try this:

Sub MyFind()
'
' find Macro
'

'
    Worksheets("PipelineData").Activate
    On Error Resume Next
    Columns("D:D").Replace What:="In Progress", Replacement:="Assessment", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    On Error GoTo 0
    
End Sub

 

[Jeffreyxx01]

Thank you for your help, I will try it out

 

[My Aswer Is This]

What does it not do for you.
It looks in column D for:
In Progress and replaces it with Assessment

What else do you want it to do?

 

[Jeffreyxx01]

I actually want the macro to replace in the column several things at the same time,

for example: Replace What:="In Progress", Replacement:="Assessment", and something else too as example " ABCD "

I have a few report where I want this to happens and you helped out on the first problem I had.

 

[Joe4]

If you have a bunch of replacements and you don't want to repeat the code, you have a few po7ssibilities.
- Store a list of the item to replace and what it is replaced with in a table somewhere in your file.
- Create some arrays to store the items to replace and what to replace them with in VBA.
In either instance, you would then just loop through the list/array in VBA to go through all the value to replace.

 

[Jeffreyxx01]

I would have 2 to 4 replacements such as:

ABCD = A
DEF = A
QWERTY = B

and so on.

 

[My Aswer Is This]

You will need to tell us what words to search for
What words to replace these words with
And where to search
Will it always be column "D"

If it's a large number of words then you will need to put these words like in column A word to search for and column B for word for replace.

Like:

Apple in A1 and Pear in B1
Cake in A2 and Pie in B2

 

[Jeffreyxx01]

Yes that would be like working on a VLOOKUP but I dont wanna use this for so little replacements,

everything that has to be replaced is in column D,

ISIS = Studytrips universities ==> Very rare to happen
Explore - BEO = OIEG StudyTrips ==> Most likely
Student Tours = Studytrips Unis ==> likely


This is what I wanna change.

 

[Joe4]

Here is the Array solution. You just want to make sure that both arrays (rep1 and rep2) are exactly the same size, and each field corresponds to the other (i.e. the first field in rep1 is replaced by the first field in rep2, etc).

Sub MyFind()
'
' MyFind Macro
'

    Dim rep1 As Variant, rep2 As Variant
    Dim i As Long
    
    Application.ScreenUpdating = False
    
'   Set arrays of values
    rep1 = Array("ABCD", "DEF", "QWERTY")
    rep2 = Array("A", "B", "B")

    Worksheets("PipelineData").Activate
    On Error Resume Next
    
'   Loop through all values and do replacements
    For i = LBound(rep1) To UBound(rep1)
        Columns("D:D").Replace What:=rep1(i), Replacement:=rep2(i), LookAt _
            :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next i
    
    On Error GoTo 0

    Application.ScreenUpdating = True
    
End Sub