find and replaced macro

K

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,553
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Hi Kylefoley

This May Help you.

Code: 
Public Sub FindReplace()
    
    Dim rngData     As Range
    '*************************************
    'Replace the Value of the Column and sheet as per your requirment
    Const lngCol = 1
    Const strSheetName = "Sheet1"
    Const strSrch = "Find text"                      'Here Type the text to Search
    Const strReplace = "Replace Text"           'Here type any text that you want to replace after Search
    '*************************************
    With ThisWorkbook.Worksheets(strSheetName)
        Set rngData = .UsedRange
    End With
    rngData.Columns(lngCol).Replace strSrch , strReplace , xlWhole
    
End Sub

Regards
Prince
 
Upvote 0
prince141987 said:
Hi Kylefoley

This May Help you.

Code: 
Public Sub FindReplace()
    
    Dim rngData     As Range
    
    Const lngCol = 1
    Const strSheetName = "Sheet2"
    Const strSrch = "exist"                      'Here Type the text to Search
    Const strReplace = "5"           'Here type any text that you want to replace after Search
    '*************************************
    With ThisWorkbook.Worksheets(strSheetName)
        Set rngData = .UsedRange
    End With
    rngData.Columns(lngCol).Replace strSrch, strReplace, xlWhole
    
    
    '*************************************
    'Replace the Value of the Column and sheet as per your requirment
    


    Const strSrch = "space"                      'Here Type the text to Search
    Const strReplace = "4"           'Here type any text that you want to replace after Search
    '*************************************
    With ThisWorkbook.Worksheets(strSheetName)
        Set rngData = .UsedRange
    End With
    rngData.Columns(lngCol).Replace strSrch, strReplace, xlWhole
    
End Sub
Click to expand...
I tried to adjust it so that I could replace multiple words at one time but I failed.
 
Upvote 0
In above code have you tried it by Changing the value of the column ie: lngCol= ?

by default it is searching your text in Column 1 you can change it as per requirement.
 
Upvote 0
Before, it should look like this:

Screenshot2013-10-03at125253AM.png


After it should look like this:

Screenshot2013-10-03at125313AM.png
 
Upvote 0
Hope this time it make some sens for us

Code: 
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
Sub callFindReplace()


    FindReplace "sheet2", "Exist", "2", 1
    FindReplace "sheet2", "Space", "3", 1
    
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
find word in sheet
Replies
2
Views
125
dreid1011
dreid1011
S
  • Question
Find and Replace Blank Cells with 0 is not working
Replies
4
Views
1K
Micron
M
L
  • Question
Step 2: VBA Macro to Sum
Replies
7
Views
234
offthelip
O
T
  • Question
Vlookup and Replace?
Replies
2
Views
120
terresf
T
D
  • Question
Find text in one cell and add data to the cell below it
Replies
7
Views
299
rlv01
R

Forum statistics

Threads
1,214,950
Messages
6,122,428
Members
449,083
Latest member
Ava19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top