使用VBA,根据列将两个表进行比对去重
1、将某一单元格数据赋值至某个变量:a = Sheets(1).Range("K3")或
a = Worksheets("sheet1").Cells(3, 11).Value
2、输出变量值:MsgBox b
3、对比两个变量是否相同的代码:
If a = b Then
MsgBox ("yes")
Else
MsgBox ("no")
End If
4、将单元格标黄:
Sheets(2).Range("B2").Interior.Color = 65535
5、取得sheet1的所有行数:
Dim cnt As Integer '定义当前列行总数
cnt = Sheets(1).UsedRange.Rows.Count '获取sheet1的所有行数
6、循环中取得某一单元格数据:
val1 = Sheets(2).Range("B" + CStr(i))
7、循环某一列的所有数据:
For i = 2 To 3
val1 = Sheets(2).Range("B" + CStr(i))
MsgBox ("The value is i is : " & val1)
Next
8、实现根据标准化代码进行比对:
Dim cnt1 As Integer '定义sheet1行总数
Dim cnt2 As Integer '定义sheet2行总数
cnt1 = Sheets(1).UsedRange.Rows.Count '获取sheet1的所有行数
cnt1 = cnt1 - 1
cnt2 = Sheets(2).UsedRange.Rows.Count '获取sheet2的所有行数
cnt2 = cnt2 - 1
For i = 2 To cnt2
val2 = Sheets(2).Range("B" + CStr(i))
For j = 2 To cnt1
val1 = Sheets(1).Range("K" + CStr(j))
If val2 = val1 Then
Sheets(2).Range("B" + CStr(i)).Interior.Color = 65535
Else
End If
Next
' MsgBox ("The value is i is : " & val1)
Next