vba表格字段对比
Sub contrast() Dim i As Long Dim j As Long Dim current_rows As Long Dim specify_rows As Long Dim nums As Long Dim file_path As Workbook Dim current_row_rng As Range Dim specify_row_rng As Range '操作表格 打开对比表 Set filePath = Workbooks.Open("D:\Alone\WXDocuments\WeChat Files\wxid_ktazc9qjr5cc22\FileStorage\File\2021-10\3.xlsx") '对比表路径 '设置表格列范围 Set current_row_rng = Range(Cells(1, 3), Cells(1, 3).End(xlDown)) Set specify_row_rng = filePath.Sheets(1).Range(filePath.Sheets(1).Cells(1, 1), filePath.Sheets(1).Cells(1, 1).End(xlDown)) current_rows = Application.WorksheetFunction.CountA(current_row_rng) specify_rows = Application.WorksheetFunction.CountA(specify_row_rng) For i = 2 To specify_rows '筛选范围 2 代表从第几行开始 102 代表执行到第几行 1 表 nums = 1 For j = 2 To current_rows '筛选范围 2 代表从第几行开始 102 代表执行到第几行 2 表 1表执行 1 次 2 表执行102次,相当于1表每一个值都会在2 表过滤一段 验证存不存在 '验证订单号是不是该订单 If filePath.Sheets(1).Cells(i, 1) = Cells(j, 3) Then ' Cells(2,3) 2代表从第几行开始 3 代表第几列的值 '是该订单 则验证两张表订单状态有没有变化 如果有则写入 If filePath.Sheets(1).Cells(i, 2) <> Cells(j, 4) Then Cells(j, 6) = filePath.Sheets(1).Cells(i, 2) End If Else '该订单不存在表中 nums+1 nums = nums + 1 End If Next j '当nums的数量与current_rows(既表的行数)一样时则代表该订单为新增订单 则把该订单写入到表中 If nums = current_rows Then Cells(current_rows + 1, 3) = filePath.Sheets(1).Cells(i, 1) Cells(current_rows + 1, 4) = filePath.Sheets(1).Cells(i, 6) Cells(current_rows + 1, 5) = filePath.Sheets(1).Cells(i, 2) Cells(current_rows + 1, 7) = filePath.Sheets(1).Cells(i, 13) Cells(current_rows + 1, 8) = filePath.Sheets(1).Cells(i, 18) Cells(current_rows + 1, 9) = filePath.Sheets(1).Cells(i, 11) current_rows = current_rows + 1 End If Next i ' 关闭对比表 filePath.Close End Sub
学习笔记