excel - 在另一个工作表中搜索第一个工作表名称并插入该列数据

Question

我有 2 个工作簿，即工作簿 A 和工作簿 B

具有 A、B、C、D 列的工作簿和具有 D、C、B、A 列的 B 工作簿。

我必须将 A 工作簿数据插入到 B 工作簿的正确列中，即插入到 A 中正确列 A 中的列，B 中的 B，C 中的 C，D 中的 D

我试过下面的代码

  Sub DEMO()
   For i = 1 To 4
      For j = 2 To 4
        For k = 2 To 4
          If Sheets(1).Cells(i, j).Value = Sheets(2).Cells(i, j).Value Then
          Sheets(2).Cells(k, j).Value = Sheets(1).Cells(j, i).Value

    End If
    Next k
    'MsgBox Sheets(1).Cells(2, 1).Value
    'MsgBox Sheets(2).Cells(2, 1).Value
Next j
Next i
End Sub

请帮忙

标签： excelvba

Answer 1

为了匹配…的列名</p>

工作表 A

列名在…</p>

工作表 B

使用循环和WorksheetFunction.Match 方法

Option Explicit

Sub MatchColumns()
    Dim wsA As Worksheet 'define worksheet A
    Set wsA = ThisWorkbook.Worksheets("A")

    Dim ColsRangeA As Range 'get column names in A
    Set ColsRangeA = wsA.Range("A1", wsA.Cells(1, wsA.Columns.Count).End(xlToLeft))

    Dim wsB As Worksheet 'define worksheet B
    Set wsB = ThisWorkbook.Worksheets("B")

    Dim ColsRangeB As Range 'get column names in B
    Set ColsRangeB = wsB.Range("A1", wsB.Cells(1, wsB.Columns.Count).End(xlToLeft))

    Dim MatchedColNo As Long

    Dim Col As Range
    For Each Col In ColsRangeA 'loop throug column names in A
        MatchedColNo = 0 'initialize
        On Error Resume Next 'test if column name can be found in worksheet B column names
        MatchedColNo = Application.WorksheetFunction.Match(Col.Value, ColsRangeB, False)
        On Error GoTo 0

        If MatchedColNo <> 0 Then 'if name was found
            wsB.Cells(2, MatchedColNo).Value = "Matches wsA col " & Col.Column
        Else 'if name didn't match
            MsgBox "no maching column found for " & Col.Value
        End If
    Next Col
End Sub