String Search
in [Programming]
|
From: LaRana6261 on 21 May 2010 19:29 I get run error 1004 having to do w/this statement: "Range("C" & lngRow) = varFound.Offset(, -1).Value".... -- EdV "Jacob Skaria" wrote: > 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 > > -- > Jacob (MVP - Excel) > > > "LaRana6261" wrote: > > > 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! > > -- > > EdV
From: LaRana6261 on 27 May 2010 17:35
Oh no...This works never mind....thank you thank you!!! -- EdV "LaRana6261" wrote: > I get run error 1004 having to do w/this statement: > > "Range("C" & lngRow) = varFound.Offset(, -1).Value".... > > > -- > EdV > > > "Jacob Skaria" wrote: > > > 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 > > > > -- > > Jacob (MVP - Excel) > > > > > > "LaRana6261" wrote: > > > > > 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! > > > -- > > > EdV |