Excel VBA 匹配和排列行 [英] Excel VBA to match and line up rows

问题描述

我有一个包含 A 到 J 列的 Excel 文档.我有包含相关数据的 K 到 N 列，但没有对齐.

I have an Excel document with columns A to J. I have columns K to N with related data, but not aligned.

我需要将 F 列中的值与 K 列中的值进行匹配，以便将它们对齐.当我移动K时，我必须一起移动L，M，N.

I need to match value from value in column F with value in column K so they are lined up. When I shift K, I have to shift L, M, N together.

我无法对 A 列到 J 列进行排序 - 它们必须保持原位.

I cannot sort columns A to J - they must remain in place.

之前的例子:

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.

The easiest way would probably be 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

这篇关于Excel VBA 匹配和排列行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！