比较excel两个表的vba

Sub compare()
‘sheets(1) 标准代码
‘sheets(2) 待校验表
‘row2 待校验表的行
row1 = Sheets(1).Range(“a65536”).End(xlUp).Row
row2 = Sheets(2).Range(“a65536”).End(xlUp).Row
‘star1 标准代码表起始行
‘star2 待校验表起始行
star1 = 2
star2 = 2
‘sheets(3) 存待校验表正确信息
‘sheets(4) 存待校验表错误信息
For i = star1 To row1
row3 = Sheets(3).Range(“a65536”).End(xlUp).Row + 1
For k = star2 To row2
‘分析的关键列
If Left(Trim(Sheets(2).Cells(k, 2)), 2) = Left(Trim(Sheets(1).Cells(i, 4)), 2) And Left(Trim(Sheets(2).Cells(k, 3)), 3) = Left(Trim(Sheets(1).Cells(i, 2)), 3) Then
‘要修政的列单元格
Sheets(2).Cells(k, 1) = Sheets(1).Cells(i, 7)
‘Exit For
Sheets(3).Cells(row3, 1) = Sheets(2).Cells(k, 5)
Sheets(3).Cells(row3, 2) = Sheets(2).Cells(k, 6)
Sheets(3).Cells(row3, 1).Font.ColorIndex = 3
Sheets(3).Cells(row3, 2).Font.ColorIndex = 3
Else
If k = row2 Then
row4 = Sheets(4).Range(“a65536”).End(xlUp).Row + 1
Sheets(4).Cells(row4, 1) = Sheets(2).Cells(i, 5)
Sheets(4).Cells(row4, 1).Font.ColorIndex = 5
Sheets(4).Cells(row4, 2) = Sheets(2).Cells(i, 6)
Sheets(4).Cells(row4, 2).Font.ColorIndex = 5
Exit For
End If
End If
Next k
Next i
End Sub

Sub compare()’sheets(1) 标准代码’sheets(2) 待校验表’row1 表准表的行’row2 待校验表的行row1 = Sheets(1).Range(“a65536”).End(xlUp).Rowrow2 = Sheets(2).Range(“a65536”).End(xlUp).Row’star1 标准代码表起始行’star2 待校验表起始行star1 = 2star2 = 2’sheets(3) 存待校验表正确信息’sheets(4) 存待校验表错误信息
For i = star1 To row1  row3 = Sheets(3).Range(“a65536”).End(xlUp).Row + 1  For k = star2 To row2        ‘分析的关键列    If Left(Trim(Sheets(2).Cells(k, 2)), 2) = Left(Trim(Sheets(1).Cells(i, 4)), 2) And Left(Trim(Sheets(2).Cells(k, 3)), 3) = Left(Trim(Sheets(1).Cells(i, 2)), 3) Then             ‘要修政的列单元格       Sheets(2).Cells(k, 1) = Sheets(1).Cells(i, 7)         ‘Exit For  Sheets(3).Cells(row3, 1) = Sheets(2).Cells(k, 5)   Sheets(3).Cells(row3, 2) = Sheets(2).Cells(k, 6)   Sheets(3).Cells(row3, 1).Font.ColorIndex = 3   Sheets(3).Cells(row3, 2).Font.ColorIndex = 3Else  If k = row2 Then    row4 = Sheets(4).Range(“a65536”).End(xlUp).Row + 1    Sheets(4).Cells(row4, 1) = Sheets(2).Cells(i, 5)    Sheets(4).Cells(row4, 1).Font.ColorIndex = 5    Sheets(4).Cells(row4, 2) = Sheets(2).Cells(i, 6)    Sheets(4).Cells(row4, 2).Font.ColorIndex = 5        Exit For    End If  End If  Next k  Next iEnd Sub

 » 转载请注明来源:网络蛀虫小窝 » 《比较excel两个表的vba》
 » 本站地址:http://www.gomoth.com

标签: