|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
短信收到,请测试:
- Sub Macro1() '有重复取第一个关键字
- Dim cnn As Object, SQL$
- Set cnn = CreateObject("ADODB.Connection")
- cnn.Open "Provider = Microsoft.Jet.Oledb.4.0;Extended Properties =Excel 8.0;Data Source =" & ThisWorkbook.FullName
- SQL = "Select a.*,b.输入关键字 as 关键字 from [Sheet1$" & Sheets("Sheet1").Range("C1").CurrentRegion.Address(0, 0) & "] a,[Sheet1$" _
- & Sheets("Sheet1").Range("a1").CurrentRegion.Address(0, 0) & "] b where instr(a.明星,b.输入关键字)"
- SQL = "select 编号,电影名,明星,first(关键字) from (" & SQL & ") group by 编号,电影名,明星 order by 编号"
- With Sheets("Sheet2")
- .UsedRange.Offset(1).ClearContents
- .[a2].CopyFromRecordset cnn.Execute(SQL)
- End With
- cnn.Close
- Set cnn = Nothing
- End Sub
- Sub Macro2() '有重复取最后一个关键字
- Dim cnn As Object, SQL$
- Set cnn = CreateObject("ADODB.Connection")
- cnn.Open "Provider = Microsoft.Jet.Oledb.4.0;Extended Properties =Excel 8.0;Data Source =" & ThisWorkbook.FullName
- SQL = "Select a.*,b.输入关键字 as 关键字 from [Sheet1$" & Sheets("Sheet1").Range("C1").CurrentRegion.Address(0, 0) & "] a,[Sheet1$" _
- & Sheets("Sheet1").Range("a1").CurrentRegion.Address(0, 0) & "] b where instr(a.明星,b.输入关键字)"
- SQL = "select 编号,电影名,明星,last(关键字) from (" & SQL & ") group by 编号,电影名,明星 order by 编号"
- With Sheets("Sheet2")
- .UsedRange.Offset(1).ClearContents
- .[a2].CopyFromRecordset cnn.Execute(SQL)
- End With
- cnn.Close
- Set cnn = Nothing
- End Sub
复制代码 |
|