find and replaced macro

[kylefoley76]

I'm going to convert a lot of words in a cell into numbers, maybe about 300. So I need to build a macro that can find and replace a lot of words. I only want it to apply to one column.

 

[kylefoley76]

I don't see why there are two subroutines. when i run the first macro it pulls up a box and i guess i'm supposed to select callfindreplace. i did and nothing happened. i tried running the callfindreplace without the first macro and it didn't work either.

 

[prince141987]

Copy both the sub in a same module and Call callfindreplace Macro it will allow you to set at which sheet what you want to replace.

 

[kylefoley76]

I don't understand what you mean. I have the two subs in one module. I run the first sub and it does not allow me to set at which sheet I want to replace.

 

[kylefoley76]

anyone?

 

[prince141987]

1:Copy both sub in a single module
2: change sub callfindreplace as public sub callfindreplace
3: Press ALT +F11 and select callfindreplace
4: Now Place the cursor any where in callfindreplace sub and then Press Green Button or run the macro


In callfindreplace there are two lines

FindReplace "sheet2", "Exist", "2", 1
it is actually taking 4 parameter
first is your sheet name,
second is Word that has to search
third is word that will be replce the searched text
and
fourth is the column in which find and replace has to implement.


By adding same such line you can find and replace multiple text in multiple column

Thats it

Hope this may make some sens to you






4

 

[kylefoley76]

ok, thanks . it was working on another workbook which i didn't know about. but i've solved the problem now.

 

[prince141987]

Finally Our effort reached at its final destination.

Cheers!!!!!!!!!!

 

[kylefoley76]

I can't get this to work anymore. I try to make changes to the macro but when I did it stopped working

Public Sub FindReplace(strSheetName As String, strSrch As String, strReplace As String, lngCol)




    With ThisWorkbook.Worksheets(strSheetName)
        .UsedRange.Columns(lngCol).Replace strSrch, strReplace, xlWhole
    End With
    
End Sub




'In this sub just pass the number of the search and replace  text
Public Sub callFindReplace()






FindReplace "Sheet9", "have", "1", 10
FindReplace "Sheet9", "and", "2", 10
FindReplace "Sheet9", "not", "3", 10
FindReplace "Sheet9", "in", "4", 10
FindReplace "Sheet9", "after", "5", 10


    
End Sub

Here's the workbook I'm using.

https://docs.google.com/file/d/0B9zzW6-3m2qGZERGZjNGUXJLMlk/edit?usp=sharing

 

[prince141987]

Public Sub FindReplace(strSheetName As String, strSrch As String, strReplace As String, strCol As String)
    With ThisWorkbook.Worksheets(strSheetName)
        .Columns(strCol).Replace strSrch, strReplace, xlWhole
    End With
    
End Sub




'In this sub just pass the number of the search and replace  text
Public Sub callFindReplace()


    FindReplace "sheet9", "Have", "2", "J"
    FindReplace "sheet9", "Not", "3", "J"
    FindReplace "sheet9", "And", "3", "J"
    
End Sub

Just Pass the Column Header as a last parameter and for example i have Passed j as it is the column in wich you have to implement find and replace.

 

[kylefoley76]

thanks for helping me out, but that's not working.