Find and Replace with VBA?

[stlpony]

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!

 

[Yongle]

Try this:

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

 

[stlpony]

Thank you for helping Yongle. Very appreciated.

Not having any success at this point. For instance, I have [TABLE="width: 329"]
<tbody>[TR]
[TD="class: xl65, width: 329"]"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 "[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Alabama,Florida,Georgia,Kentucky,Mississippi,North Carolina,South Carolina,Tennessee,Maryland,Virginia,West Virginia"

Any idea? Thanks again.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Yongle]

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

txtFind = r.Value

instead of

txtFind = "U.S. " & r.Value)

 

[stlpony]

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

txtFind = r.Value

instead of

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!!!!!!

 

[Joe4]

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.