我有一个 Excel 文档,其中包含 A 到 J 列。K 到 N 列包含相关数据,但未对齐。
我需要将 F 列中的值与 K 列中的值进行匹配,以便它们对齐。当我移动K时,我必须一起移动L、M、N。
我无法对 A 到 J 列进行排序 - 它们必须保留在原处。
之前的例子:
A B C D E F G H I J K L M N
data data data data data record1 data data data data record3 data data data
data data data data data record2 data data data data record1 data data data
data data data data data record3 data data data data
data data data data data record4 data data data data
之后的示例:
A B C D E F G H I J K L M N
data data data data data record1 data data data data record1 data data data
data data data data data record2 data data data data
data data data data data record3 data data data data record3 data data data
data data data data data record4 data data data data
最简单的方法可能是 ADO。
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer
''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.
strFile = ActiveWorkbook.FullName
''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT * " _
& "FROM [Sheet2$A1:J5] a " _
& "LEFT JOIN [Sheet2$K1:N5] b " _
& "ON a.F=b.k "
rs.Open strSQL, cn, 3, 3
''Pick a suitable empty worksheet for the results
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs
''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)