find and replace by looking at first 6 characters

[sparky101747]

I am looking for a macro to look at the first 6 characters of column A and if it matched the value to replace in column F in the same row. there are about 400 different variations that need to be looked at but there are only 2 results.

so in column A i would have:

167-02-254
167-03-548
167-14-565
170-25-554
167 02 456
167 03 6456


in column F in the same row I would replace the contents of that cell with either "Los Altos" or "Los Altos Hills".

Note: i don't know if it is possible to look at charaters 1-3,5, & 6 because there may or may not be a "-" in the 4 spot. For instance either "167-02" or "167 02" would both equal "Los Altos".


Thank You

 

[Domenic]

Let Sheet2, A1:B8, contain the following lookup table...

<TABLE style="WIDTH: 257pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=343><COLGROUP><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6107" width=167><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436" width=176><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 125pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=167>167-15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 132pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=176>Los Altos</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>167-18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 132pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=176>Los Altos</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>167-33</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 132pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=176>Los Altos</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>167-44</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 132pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=176>Los Altos</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>167-22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 132pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=176>Los Altos</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>175-27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 132pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=176>Los Altos Hills</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>175-02</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 132pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=176>Los Altos Hills</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>182-22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 132pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=176>Los Altos Hills</TD></TR></TBODY></TABLE>

Add to the above table, as required. Then, assuming that Sheet1, A1:F16 contains the data, try the following macro...

[FONT=Verdana][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=darkblue]Sub[/COLOR] test()[/FONT]
 
[FONT=Verdana] [COLOR=darkblue]Dim[/COLOR] WS1 [COLOR=darkblue]As[/COLOR] Worksheet[/FONT]
[FONT=Verdana] [COLOR=darkblue]Dim[/COLOR] WS2 [COLOR=darkblue]As[/COLOR] Worksheet[/FONT]
[FONT=Verdana] [COLOR=darkblue]Dim[/COLOR] LookupTable [COLOR=darkblue]As[/COLOR] Range[/FONT]
[FONT=Verdana] [COLOR=darkblue]Dim[/COLOR] LookupValue [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Verdana] [COLOR=darkblue]Dim[/COLOR] LastRow1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
[FONT=Verdana] [COLOR=darkblue]Dim[/COLOR] LastRow2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
[FONT=Verdana] [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
 
[FONT=Verdana] Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR][/FONT]
 
[FONT=Verdana] [COLOR=darkblue]Set[/COLOR] WS1 = Worksheets("Sheet1")[/FONT]
[FONT=Verdana] [COLOR=darkblue]Set[/COLOR] WS2 = Worksheets("Sheet2")[/FONT]
 
[FONT=Verdana] [COLOR=darkblue]With[/COLOR] WS2[/FONT]
[FONT=Verdana]     LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row[/FONT]
[FONT=Verdana]     [COLOR=darkblue]Set[/COLOR] LookupTable = Range(.Cells(1, "A"), .Cells(LastRow2, "B"))[/FONT]
[FONT=Verdana] [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR][/FONT]
 
[FONT=Verdana] [COLOR=darkblue]With[/COLOR] WS1[/FONT]
[FONT=Verdana]     LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row[/FONT]
[FONT=Verdana]     [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] LastRow1[/FONT]
[FONT=Verdana]         LookupValue = Replace(Left(.Cells(i, "A").Value, 6), " ", "-")[/FONT]
[FONT=Verdana]         .Cells(i, "F").Value = Application.VLookup(LookupValue, LookupTable, 2, 0)[/FONT]
[FONT=Verdana]     [COLOR=darkblue]Next[/COLOR] i[/FONT]
[FONT=Verdana] [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR][/FONT]
 
[FONT=Verdana] Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR][/FONT]
 
[FONT=Verdana] MsgBox "Completed...", vbInformation[/FONT]
 
[FONT=Verdana][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

 

[sparky101747]

Works Good except it changed my other data that didn't need to be changed to #NA. can we put an if in there so if it doesn't find it on the lookup table it does nothing

 

[Domenic]

First, add the following to the variable declarations...

[FONT=Verdana]    [COLOR=darkblue]Dim[/COLOR] ReturnValue [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR][/FONT]

Then replace...

[FONT=Verdana]    [COLOR=darkblue]With[/COLOR] WS1[/FONT]
[FONT=Verdana]       LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row[/FONT]
[FONT=Verdana]       [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] LastRow1[/FONT]
[FONT=Verdana]           LookupValue = Replace(Left(.Cells(i, "A").Value, 6), " ", "-")[/FONT]
[FONT=Verdana]           .Cells(i, "F").Value = Application.VLookup(LookupValue, LookupTable, 2, 0)[/FONT]
[FONT=Verdana]       [COLOR=darkblue]Next[/COLOR] i[/FONT]
[FONT=Verdana]   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR][/FONT]

with

[FONT=Verdana]    [COLOR=darkblue]With[/COLOR] WS1[/FONT]
[FONT=Verdana]       LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row[/FONT]
[FONT=Verdana]       [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] LastRow1[/FONT]
[FONT=Verdana]           LookupValue = Replace(Left(.Cells(i, "A").Value, 6), " ", "-")[/FONT]
[FONT=Verdana]           ReturnValue = Application.VLookup(LookupValue, LookupTable, 2, 0)[/FONT]
[FONT=Verdana]           [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsError(ReturnValue) [COLOR=darkblue]Then[/COLOR][/FONT]
[FONT=Verdana]               .Cells(i, "F").Value = ReturnValue[/FONT]
[FONT=Verdana]           [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]
[FONT=Verdana]       [COLOR=darkblue]Next[/COLOR] i[/FONT]
[FONT=Verdana]   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR][/FONT]

 

[sparky101747]

Worked like a charm, thank you

 

[Domenic]

You're very welcome!