比较来自两个单独的excel文件的特定单元格

我是VBA的新手,非常感谢您的帮助。

对于我的工作,我时不时地必须比较两个数据库,一个来自患者文档系统,另一个来自设备数据库。这两个数据库都导出为ex​​cel文件。我想比较每个excel文件的一列(第一个文件的B列,第二个文件的M列)中的单元格,并确保它们的内容相同。如果相同,则该单元应变为绿色,否则为红色。这将使发现打字错误更加容易,并节省了大量工作。 由于我不想每次都执行此操作,因此我制作了一个用户表单,可以在其中加载两个Excel工作表并单击“比较”。到目前为止,一切都很好。 我的问题(新手)是:如何比较这两个特定列(位于单独的excel文件中)的每个单元格?我想出了以下代码,该代码是在单击“比较”按钮后执行的:

Sub CommandButton3_Click()

Dim BBraunFile
Dim ICMFile
Dim CellsBBraun
Dim CellsICM
Dim CellA
Dim CellB

Set BBraunFile = Workbooks.Open(strFileToOpenBbraun)
Set ICMFile = Workbooks.Open(strFileToOpenICM)

CellsBBraun = BBraunFile.Worksheets("0_Standard-Pat.-Profil").Range("b4:b5000")
CellsICM = ICMFile.Worksheets("ExternalIDs").Range("M2:M5000")

    For Each CellA In CellsBBraun
        Set CellB = ICMFile.Worksheets("ExternalIDs").Range(Cell.Row, 13)
        If CellA.Value = CellB.Value Then
                CellA.Interior.ColorIndex = 3
            Else
                CellA.Interior.ColorIndex = 4
        End If
    Next CellA

End Sub

运行脚本时,出现运行时错误“ 424:对象必需”。我可能搞砸了语法,但不知道如何解决。我环顾四周,但找不到答案。请帮忙?

评论
  • 死一般的静
    死一般的静 回复

    This uses a dictionary, you could use any container really but dictionaries have .exists which helps here.

    您还应该声明所有变量的类型,当前是所有变量。

        Dim BBraunFile As Workbook
        Dim ICMFile As Workbook
        Dim CellsBBraun As Range
        Dim CellsICM As Range
    
        Set BBraunFile = Workbooks.Open(strFileToOpenBbraun)
        Set ICMFile = Workbooks.Open(strFileToOpenICM)
    
        'Set your objects
        Set CellsBBraun = BBraunFile.Worksheets("0_Standard-Pat.-Profil").Range("b4:b5000")
        Set CellsICM = ICMFile.Worksheets("ExternalIDs").Range("M2:M5000")
    
        Dim valdict As Object ' This is late binding add in the scripting runtime library for early binding
        Set valdict = CreateObject("Scripting.Dictionary") ' This is late binding add in the scripting runtime library for early binding
    
        Dim CellA As Range
    
        For Each CellA In CellsICM 'Iterate through workbook we aren't formatting
            valdict(CellA.Value) = "" ' Just populating keys we dont need an item
        Next CellA
    
        For Each CellA In cellsbbraum 'Iterate through workbook we are formatting
            If valdict.exists(CellA.Value) Then 'See if the value is in the dictionary
                CellA.Interior.ColorIndex = 3
            Else
                CellA.Interior.ColorIndex = 4
            End If
        Next CellA