String Search

[LaRana6261]

Hello,

I have two tables where I want find the best match according to the
description on both tables. The result will be the corresponding code in the
second table.
Example:

first table
code description
101 agri
102 single family
103 mobile home
104 vacant land
105 recr

Second table

Code Description
1000 Agricultural
1002 Single Family Residence
1003 Recreational
1004 Vacant

When both tables are matched, the macro should look for the best fit in
table 2 and returns the code. The 'Best fit' will depend on words that match
(maybe the first 4 to 5 letters) and a count of words. The match with the
highest count will define which code should be pulled....

Greatly appreciated!

 

[PY & Associates]

Hello,

I have two tables where I want find the best match according to the
description on both tables. The result will be the corresponding code in the
second table.
Example:

first table
code          description
101            agri
102            single family
103            mobile home
104            vacant land
105            recr

Second table

Code      Description
1000      Agricultural
1002     Single Family Residence
1003     Recreational
1004     Vacant

When both tables are matched, the macro should look for the best fit in
table 2 and returns the code. The 'Best fit' will depend on words that match
(maybe the first 4 to 5 letters) and a count of words. The match with the
highest count will define which code should be pulled....

Greatly appreciated!

Like "agri*"

 

[LaRana6261]

Thanks, but table 1 will be different each time as I review different data
files. The macro will have to be flexible enough to pick the first 4 to 5
letter in description and search in table 2. Then, return the code in table 2.

 

[PY & Associates]

Thanks, but table 1 will be different each time as I review different data
files. The macro will have to be flexible enough to pick the first 4 to 5
letter in description and search in table 2. Then, return the code in table 2.

--
EdV






- Show quoted text -

Perhaps

n=inputbox("Number of characters to match")
strLike=left(cell, n) & "*"
like strLike

 

[Jacob Skaria]

Hi

Assuming your first table in Sheet1 ColA/B and the second table in Sheet2
ColA/B try the below macro with Sheet1 activated..

Sub Macro()
Dim rngData As Range, varFound As Range, lngRow As Long

Set rngData = Sheets("Sheet2").Range("A1:B10")

For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Range("B" & lngRow) <> "" Then
Set varFound = rngData.Find(Split(Range("B" & lngRow))(0) & "*", _
LookIn:=xlValues, lookat:=xlWhole)
If Not varFound Is Nothing Then
Range("C" & lngRow) = varFound.Offset(, -1).Value
End If
End If
Next
End Sub

 

[LaRana6261]

I get run error 1004 having to do w/this statement:

"Range("C" & lngRow) = varFound.Offset(, -1).Value"....

 

[LaRana6261]

Oh no...This works never mind....thank you thank you!!!