Find and Replace with VBA?

stlpony

New Member
Joined
Mar 8, 2018
Messages
39
Hello,

Trying to do some advanced find and replace. Would normally use index match, but there's a few reasons I think there may be a better VBA solution.

I have the names of United States Regions in Column A. Sometimes there are multiple regions ... I.e., Cell A5 could say "U.S. Midwest" and Cell A6 could say "U.S. Midwest, U.S. Northeast"

In Column B I have the names of all regions in the united states from B2:B13 Cell B7 says "Northeast"

In Column C I have the states that comprise each region, from C2:C13. Cell C7 says "Main,Vermont,Connecticut," etc ...

How do I tell VBA to find and replace the region names in Column A, with the end states in Column C?

Any tips? Thanks!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try this:

Code:
Sub ReplaceRegionsWithEndStates()
    Dim cel As Range, regn As Range, r As Range, txt As String, txtFind As String, txtRepl As String
    Application.ScreenUpdating = False
    With ActiveSheet
        Set regn = .Range("B2:B13")
        For Each cel In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            txt = cel.Value
            For Each r In regn
                txtFind = "U.S. " & r.Value
                txtRepl = r.Offset(, 1).Value
                txt = Replace(txt, txtFind, txtRepl)
            Next r
            cel.Value = txt
        Next cel
    End With
    Application.ScreenUpdating = True
End Sub

Notes
- code looks in column A (beginning A2) for string U period S period space + Region
- and replaces that string with end states
 
Last edited:
Upvote 0
Thank you for helping Yongle. Very appreciated.

Not having any success at this point. For instance, I have
"U.S. Southeast" in cell A7,

Then I have my table matching regions and to states. Specifiically, B5 has "U.S. Southeast" and C5 has "
Alabama,Florida,Georgia,Kentucky,Mississippi,North Carolina,South Carolina,Tennessee,Maryland,Virginia,West Virginia"

Any idea? Thanks again.

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
You have changed your story! Why did you leave out the "U.S. " in post#1 ?
Post#3: B5 has "U.S. Southeast"
Post#1: B7 says "Northeast"


That is why it does not give yiou what you want

Try
Code:
txtFind = r.Value
instead of
Code:
txtFind = "U.S. " & r.Value)
 
Last edited:
Upvote 0
You have changed your story! Why did you leave out the "U.S. " in post#1 ?
Post#3: B5 has "U.S. Southeast"
Post#1: B7 says "Northeast"


That is why it does not give yiou what you want

Try
Code:
txtFind = r.Value
instead of
Code:
txtFind = "U.S. " & r.Value)

Perfect!!!! Yes I slightly changed my story, bc I wanted to originally be able to have it simple to understand.

Thanks!!!!!!
 
Upvote 0
Yes I slightly changed my story, bc I wanted to originally be able to have it simple to understand.
That is something that happens quite often. You want to be careful to not oversimplify the problem so much, that the solution you get will not actually solve your actual problem (especially if you are not comfortable making the necessary modifications the code you need yourself). It is usually better to ask for what you really want.
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,463
Members
448,573
Latest member
BEDE

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