VBA Help: Replace Text in different Sheet

A

Adamzurick

New Member
Joined
Jun 26, 2008
Messages
20
Hi all, I'm new to VBA and while trying to learn this new language, need to figure out a way to do something for work ahead of my current skill level.

I have two sheets, in each there are hundreds of rows of the three columns listed below (I'm only using 1 row but would need this using a range). Using Sheet 1 as the key, I want to replace Sheet 2 column B and C wherever the name matches all occurrences.

I think I found what code to use, I just don't know how to use it "TextRange2.Replace". Any help would be WONDERFUL!!!

[TABLE="width: 416"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Country[/TD]
[TD]Region[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]America[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Country[/TD]
[TD]Region[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]Spain[/TD]
[TD]Madrid[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 2 (After Macro)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Country[/TD]
[TD]Region[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]America[/TD]
[TD]New York[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Adamzurick said:
Hi all, I'm new to VBA and while trying to learn this new language, need to figure out a way to do something for work ahead of my current skill level.

I have two sheets, in each there are hundreds of rows of the three columns listed below (I'm only using 1 row but would need this using a range). Using Sheet 1 as the key, I want to replace Sheet 2 column B and C wherever the name matches all occurrences.

I think I found what code to use, I just don't know how to use it "TextRange2.Replace". Any help would be WONDERFUL!!!

[TABLE="width: 416"]
<tbody>[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Country[/TD]
[TD]Region[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]America[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Country[/TD]
[TD]Region[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]Spain[/TD]
[TD]Madrid[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 2 (After Macro)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Country[/TD]
[TD]Region[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]America[/TD]
[TD]New York[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Hi Adamzurick,

If possible, could you post screenshots of at least 10 rows? I'm having an issue understanding :)

Thank you.
 
Upvote 0
Try this...

Code: 
[color=darkblue]Sub[/color] Replace_Country_Region()
    
    [color=darkblue]Dim[/color] v [color=darkblue]As[/color] [color=darkblue]Variant[/color], i [color=darkblue]As[/color] [color=darkblue]Long[/color], Found [color=darkblue]As[/color] Range, FirstFound [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    [color=darkblue]With[/color] Sheets("Sheet1")
        v = .Range("A2", .Range("C" & Rows.Count).End(xlUp)).Value
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]With[/color] Sheets("Sheet2")
        [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](v, 1) [color=darkblue]To[/color] [color=darkblue]UBound[/color](v, 1)
            [color=darkblue]Set[/color] Found = .Range("A:A").Find(v(i, 1), , xlValues, xlWhole, xlByRows, xlNext, [color=darkblue]False[/color])
            [color=darkblue]If[/color] [color=darkblue]Not[/color] Found [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                FirstFound = Found.Address
                [color=darkblue]Do[/color]
                    Found.Offset(, 1).Value = v(i, 2)
                    Found.Offset(, 2).Value = v(i, 3)
                    [color=darkblue]Set[/color] Found = .Range("A:A").FindNext(After:=Found)
                [color=darkblue]Loop[/color] [color=darkblue]While[/color] Found.Address <> FirstFound
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
End [color=darkblue]Sub[/color]
 
Upvote 0
I dont think that did it. I tried to manipulate the code to fit my sheet but it didnt work. I've posted a more detailed version of what i'm trying to do below. Also, i noticed the code "first found"...I'm not certain of what this does but I need every instance of a name in sheet 2 matched to sheet 1, not just the first.

Sorry for the explanation the first time, I'll try to get more detailed with this next one:

I have two sheets, in each there are hundreds of rows of names. I want to use Sheet 1 as a "key", to basically replace every instance of the same name in Sheet 2. Those names that do not match exactly, will stay the same. So in the below, Adam, Bob, Susie and Ben were changed because the names are exact matches in Sheet 1 and 2, however Dan was not in Sheet 1 so neither his country nor region changed. Ultimately a vlookup would do the trick, but there is so much info that i'd like to keep it clean and in VBA.

I've also listed the rows and columns. From sheet to sheet, the information is not in identical places (ie the start of the information in Sheet 1 starts in B7 but the information I'm replacing in Sheet 2 starts in C2). While the information is not in the same place, it is in the same order...name, country, region...in both sheets. I am likely going to have to manipulate the code to move the range of rows and maybe the columns as other information comes in so an quick explanation of where that comes in would be helpful.

Thank you AlphaFrog for taking a wack at this the first time, I feel my description wasnt good enough so my apologies. And thank you in advance for the person who solves this of me!!!








--SHEET 1--
[TABLE="width: 551"]
<tbody>[TR]
[TD]Column/Row
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NAME
[/TD]
[TD]COUNTRY
[/TD]
[TD]REGION
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]ADAM
[/TD]
[TD]AMERICA
[/TD]
[TD]NEW YORK
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]BOB
[/TD]
[TD]ENGLAND
[/TD]
[TD]LONDON
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]CHRIS
[/TD]
[TD]SPAIN
[/TD]
[TD]MADRID
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]SUSIE
[/TD]
[TD]MEXICO
[/TD]
[TD]MEXICO CITY
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]BEN
[/TD]
[TD]ITALY
[/TD]
[TD]ROME
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]…(THERE ARE ABOUT A HUNDRED NAMES)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--SHEET 2--
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column/Row
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NAME
[/TD]
[TD]COUNTRY
[/TD]
[TD]REGION
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ADAM
[/TD]
[TD]AMERICA
[/TD]
[TD]CALIFORNIA
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]DAN
[/TD]
[TD]CANADA
[/TD]
[TD]MONTREAL
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]BOB
[/TD]
[TD]SPAIN
[/TD]
[TD]BARCELONA
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]SUSIE
[/TD]
[TD]SWITZERLAND
[/TD]
[TD]ZURICH
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]BEN
[/TD]
[TD]GERMANY
[/TD]
[TD]BERLIN
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]…(THERE ARE HUNDREDS OF ROWS OF NAMES HERE TOO)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--THIS IS WHAT SHEET 2 SHOULD LOOK LIKE AFTER MACRO--
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column/Row
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NAME
[/TD]
[TD]COUNTRY
[/TD]
[TD]REGION
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ADAM
[/TD]
[TD]AMERICA
[/TD]
[TD]NEW YORK
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]DAN
[/TD]
[TD]CANADA
[/TD]
[TD]MONTREAL
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 3
[/TD]
[TD]BOB
[/TD]
[TD]ENGLAND
[/TD]
[TD]LONDON
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]SUSIE
[/TD]
[TD]MEXICO
[/TD]
[TD]MEXICO CITY
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]BEN
[/TD]
[TD]ITALY
[/TD]
[TD]ROME
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]Here Adam, Bob, Susie and Ben were changed to what's in sheet 1, Dan was not in the sheet one "key" so neither his country nor region changed.
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Your original explanation was good. The procedure does find every instance of an exact match of the "Key" name.

The data locations were changed in the code below. It produces the same end result as you provided. Added notes as well though it's not intended to be a complete tutorial on VBA programing.

Code: 
[color=darkblue]Sub[/color] Replace_Country_Region()
    
    [color=darkblue]Dim[/color] v [color=darkblue]As[/color] [color=darkblue]Variant[/color], i [color=darkblue]As[/color] [color=darkblue]Long[/color], Found [color=darkblue]As[/color] Range, FirstFound [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    [color=green]' "Key" data range on Sheet1[/color]
    [color=green]' From cell B7 to the last used row in column D[/color]
    [color=darkblue]With[/color] Sheets("Sheet1")
        v = .Range("B7", .Range("D" & Rows.Count).End(xlUp)).Value
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]With[/color] Sheets("Sheet2")
        [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](v, 1) [color=darkblue]To[/color] [color=darkblue]UBound[/color](v, 1) [color=green]' Search for each "Key" name[/color]
            [color=green]'Search Sheet2 Column C for each Key name[/color]
            [color=darkblue]Set[/color] Found = .Range("C:C").Find(v(i, 1), , xlValues, xlWhole, xlByRows, xlNext, [color=darkblue]False[/color])
            [color=darkblue]If[/color] [color=darkblue]Not[/color] Found [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]    [color=green]'Test if "Key" name was found[/color]
                FirstFound = Found.Address  [color=green]'cell address of the First occurence of the found Name[/color]
                [color=darkblue]Do[/color]
                    Found.Offset(, 1).Value = v(i, 2)   [color=green]' copy country to matched Key Name[/color]
                    Found.Offset(, 2).Value = v(i, 3)   [color=green]' copy region to matched Key Name[/color]
                    [color=darkblue]Set[/color] Found = .Range("C:C").FindNext(After:=Found)    [color=green]'Search for next occurence of "Key" name in sheet2 column C[/color]
                [color=darkblue]Loop[/color] [color=darkblue]While[/color] Found.Address <> FirstFound  [color=green]'Loop until the address of the first occurence is found again.[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
End [color=darkblue]Sub[/color]

If some instances of a Key name are not matched, it's likely the Name is not an exact match. The match search is not case sensitive (John will equal john), but any spelling difference or a trailing space will not match; (Jon <> John) or ("John" <> "John "),
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Find and Replace Value in List Named Range Reference
Replies
0
Views
537
arembr01
A
J
  • Question Question
VBA code to find replace formula
Replies
7
Views
731
jamesmitchell
J
N
  • Solved
Find and Replace VBA from a list in a 2nd sheet
Replies
4
Views
576
Lee_ray
L
J
  • Question Question
VBA Code for split text based on variable length and replace
Replies
5
Views
195
Peter_SSs
Peter_SSs
M
  • Question Question
Match cell contents exactly for find and replace based on a table
Replies
3
Views
727
mumps
mumps

Forum statistics

Threads
1,223,244
Messages
6,170,976
Members
452,372
Latest member
Natalie18

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