VBA Help: Replace Text in different Sheet

[Adamzurick]

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

Sheet 1
Name	Country	Region
Adam	America	New York
Sheet 2
Name	Country	Region
Adam	Spain	Madrid
Sheet 2 (After Macro)
Name	Country	Region
Adam	America	New York

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[longtimelurker]

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

Sheet 1
Name	Country	Region
Adam	America	New York
Sheet 2
Name	Country	Region
Adam	Spain	Madrid
Sheet 2 (After Macro)
Name	Country	Region
Adam	America	New York

<tbody>
</tbody>

Hi Adamzurick,

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

Thank you.

 

[AlphaFrog]

Try this...

[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]

 

[Adamzurick]

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

Column/Row	B	C	D
	NAME	COUNTRY	REGION
7	ADAM	AMERICA	NEW YORK
8	BOB	ENGLAND	LONDON
9	CHRIS	SPAIN	MADRID
10	SUSIE	MEXICO	MEXICO CITY
11	BEN	ITALY	ROME
	…(THERE ARE ABOUT A HUNDRED NAMES)
--SHEET 2--
Column/Row	C	D	E
	NAME	COUNTRY	REGION
2	ADAM	AMERICA	CALIFORNIA
3	DAN	CANADA	MONTREAL
4	BOB	SPAIN	BARCELONA
5	SUSIE	SWITZERLAND	ZURICH
6	BEN	GERMANY	BERLIN
	…(THERE ARE HUNDREDS OF ROWS OF NAMES HERE TOO)
--THIS IS WHAT SHEET 2 SHOULD LOOK LIKE AFTER MACRO--
Column/Row	C	D	E
	NAME	COUNTRY	REGION
1	ADAM	AMERICA	NEW YORK
2	DAN	CANADA	MONTREAL
3	BOB	ENGLAND	LONDON
4	SUSIE	MEXICO	MEXICO CITY
5	BEN	ITALY	ROME
	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.

<tbody>
</tbody>

 

[AlphaFrog]

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.

[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 "),

 

[Adamzurick]

This worked like a charm. Thank you sooo soo much AlphaFrog.

 

[AlphaFrog]

You're welcome.