使用excel vba比较列标题 [英] comparing column headers using excel vba

问题描述

我是excel vba的新手。我正在尝试比较两个工作簿之间的列标题。这可能使用自动比较并添加0的vba

I am new to excel vba., I am trying to compare column headers between two workbooks.is this possible using vba that automatically compares and adds 0 's in it

例如

我有两个工作簿，我需要比较两个列标题。



例如：工作簿1有以下列：



columnA | columnC |栏E
$


工作簿2有以下栏目



columnA | columnB | columnC | columnD |工作簿1中的栏目E¥


输出应为b

columnA | columnB | columnC | columnD |栏目E¥


..                 0        ..        ..           0        



columnB和columnD在工作簿1中添加，因为工作簿2中存在这些列，其中添加了0。

I have two workbooks where i need to compare both column headers.

Ex: the workbook1 has following columns:

columnA | columnC | columnE

the workbook2 has following columns

columnA | columnB | columnC | columnD | columnE

output in workbook1 should be

columnA | columnB | columnC | columnD | columnE

..                 0        ..        ..           0        

columnB and columnD are added in workbook1 as these columns exists in workbook2 added 0 in it.

推荐答案

Sub pMain()
  Dim ws1 As Excel.Worksheet
  Dim ws2 As Excel.Worksheet
  Dim lLastRow As Long
  Dim lCol1 As Long
  Dim lCol2 As Long
  Dim lMatch As Long
  
  'Change to suit.
  'ws1 is the worksheet missing columns
  'ws2 is the worksheet with all columns
  Set ws1 = Workbooks("Pasta1").Worksheets("Plan1")
  Set ws2 = Workbooks("Pasta1").Worksheets("Plan2")
  
  lCol1 = 1
  lLastRow = ws1.Cells.Find("*", ws1.Cells(1), xlValues, xlPart, xlByRows, xlPrevious).Row
  For lCol2 = 1 To ws2.Cells(1, ws2.Columns.Count).End(xlToLeft).Column
    lMatch = pMatch(ws2.Cells(1, lCol2), ws1.Rows(1))
    If lMatch = 0 Then
      ws1.Columns(lCol1 + 1).Insert
      ws1.Cells(1, lCol1 + 1) = ws2.Cells(1, lCol2)
      ws1.Cells(2, lCol1 + 1).Resize(lLastRow - 1) = 0
    Else
      lCol1 = lMatch
    End If
  Next lCol2
End Sub

Private Function pMatch(vValue As Variant, _
                        vArray As Variant) As Long
  Dim ret As Long

  On Error Resume Next
  ret = WorksheetFunction.Match(CDbl(vValue), vArray, 0)
  If ret = 0 Then ret = WorksheetFunction.Match(CStr(vValue), vArray, 0)
  
  pMatch = ret
End Function

这篇关于使用excel vba比较列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！