excel - 在 Ms Excel VBA 中将 2 对数据与循环进行比较

Question

有谁可以帮助我，请看一下图片 我附上了。
我想比较来自 2 个不同 excel 文件的 2 对数据，车站 (左文件 列 B )与 车站 (右文件 列 A )和 时间 (左文件 第 1 行 )与 趋向 (右文件 F 列 )。
左边的文件是我即将完成的报告，右边的文件是引用数据。如果站点和时间数据相互匹配，它将填充 "1" ，如果不是，它将保留 空 .
数据将从单元格 开始填充C2 直到 Z32 .我坚持使用我使用的 FOR 和 IF 循环。这是一个例子:

单元格 C2 将提交 "1" bcs有站 2000001(单元格 A2)在 00UTC(单元格 F2)在 正确的文件 .

单元格 E2 会留下空 bcs有站 2000001 但不是 在 02UTC 在 正确的文件 .

单元格 C3 会留下空 bcs有站 2000002 但不是 在 00UTC 在 正确的文件 .

Dim countSM As Long
Dim countSS As Long
Dim countWM As Long
Dim countWS As Long
Dim resultCol As Long
Dim resultRow As Long

Dim lastSM As Long
Dim lastSS As Long
Dim lastWM As Long
Dim lastWS As Long
Dim lastRCol As Long
Dim lastRRow As Long

lastSM = wb1.Sheets("Sheet1").Cells(Rows.count, 1).End(xlUp).Row
lastSS = wb2.Sheets("Worksheet").Cells(Rows.count, 1).End(xlUp).Row
lastWM = wb1.Sheets("Sheet1").Cells(1, Columns.count).End(xlToLeft).Column
lastWS = wb2.Sheets("Worksheet").Cells(Rows.count, 1).End(xlUp).Row
lastRCol = wb1.Sheets("Sheet1").Cells(1, Columns.count).End(xlToLeft).Column
lastRRow = wb1.Sheets("Sheet1").Cells(Rows.count, 1).End(xlUp).Row

For countSM = 2 To lastWM
For countSS = 2 To lastWS
If wb1.Sheets("Sheet1").Cells(countSM, "B") = wb2.Sheets("Worksheet").Cells(countSS, "A") Then
    For countWM = 3 To lastWM
    For countWS = 2 To lastWS
    If wb1.Sheets("Sheet1").Cells(1, countWM) = wb2.Sheets("Worksheet").Cells(countWS, "F") Then
        For resultRow = 2 To lastRRow
        For resultCol = 3 To lastRCol
        wb1.Sheets("Sheet1").Cells(resultRow, resultCol) = "1"
        Next resultCol
        Next resultRow
        Next countSS
    ElseIf wb1.Sheets("Sheet1").Cells(1, countWM) <> wb2.Sheets("Worksheet").Cells(countWS, "F") Then
        wb1.Sheets("Sheet1").Cells(resultRow, resultCol) = ""
        Next countWM
    End If
    Next countSS
ElseIf wb1.Sheets("Sheet1").Cells(countSM, "B") <> wb2.Sheets("Worksheet").Cells(countSS, "A") Then
        wb1.Sheets("Sheet1").Cells(resultRow, resultCol) = ""
        Next countSM
End If

Answer 1

我制作了一个可能对你有用的代码。只需计算有多少行获得了您要检查的电台和 UTC 值。如果答案为零，请将单元格留空。如果不是，则返回 1。
我的代码是在 上设计的相同的工作簿 但它可以很容易地适应 2 个不同的工作簿。
我的假数据集:

我的代码:

Sub test()
'<------>
'
'
'
'
'YOUR CODE TO OPEN BOTH FILES
'
'
'

'<---->



Dim LeftSheet As Worksheet
Dim RightSheet As Worksheet
Dim MyData As Range 'range to store the data (right file)
Dim LR As Long 'Last row of left file, column Station
Dim LC As Long 'Lastcolumn of left file, (whatever UTC it is)
Dim i As Long
Dim zz As Long
Dim MiF As WorksheetFunction
Set MiF = WorksheetFunction
Dim MyStation As String
Dim MyUTC As String


'Probably you'll need just to adjust references to worksheets from different workbooks

Set LeftSheet = ThisWorkbook.Worksheets("Destiny")
Set RightSheet = ThisWorkbook.Worksheets("Source")

'we store all data into array
Set MyData = RightSheet.Range("A1").CurrentRegion

'data starts at index 2, and we want data from columns 1 and 6 on the range
'Columns 1 and 6 mean columns A and F

'I guess maybe you'll need to adapt this too.
With LeftSheet
    LR = .Range("B" & .Rows.Count).End(xlUp).Row
    LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
    
    'we count how many rows got the station and tendancy value (intersection) on the right file
    ' if the count is 0, do nothing. If not zero, return 1 on the cell
    'our references will be always at column 2 and row 1
    
    For i = 2 To LR Step 1 'we start at row 2 on left file
    
        MyStation = .Range("B" & i).Value
        For zz = 3 To LC Step 1 'we start at column 3, that means column C
            MyUTC = .Cells(1, zz).Value
            If MiF.CountIfs(MyData.Columns(1), MyStation, MyData.Columns(6), MyUTC) <> 0 Then .Cells(i, zz).Value = 1
        Next zz
    Next i

End With


'clean variables
Set MyData = Nothing
Set LeftSheet = Nothing
Set RightSheet = Nothing
End Sub

执行代码后输出: