自己用的一個小工具:載點
使用方法:
圖1.
VBA設定教學
按鈕1:B行重複反黃(圖1)
1.A行為原始資料,B行貼上要被比較的資料。
2.點選按鈕。
3.反黃表示已重複。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Private Sub CommandButton1_Click() | |
X1 = Application.CountA(Sheets("SHEET1").Range("A:A")) | |
X2 = Application.CountA(Sheets("SHEET1").Range("B:B")) | |
For I = 1 To X1 Step 1 | |
For J = 1 To X2 Step 1 | |
If Val(Sheets("SHEET1").Range("A" & I)) = Val(Sheets("SHEET1").Range("B" & J)) Then | |
Sheets("SHEET1").Range("B" & J).Interior.Color = QBColor(14) | |
End If | |
Next J | |
Next I | |
End Sub |
圖2.
操作與按鈕1類似。
主要差別是關鍵字比對,當a行的儲存格含有b儲存格的文字時,則b儲存格反黃表示。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Private Sub CommandButton2_Click() | |
X1 = Application.CountA(Sheets("SHEET1").Range("A:A")) | |
X2 = Application.CountA(Sheets("SHEET1").Range("B:B")) | |
For I = 1 To X1 Step 1 | |
For J = 1 To X2 Step 1 | |
If InStr(Sheets("SHEET1").Range("A" & I), Sheets("SHEET1").Range("B" & J)) > 0 Then | |
Sheets("SHEET1").Range("B" & J).Interior.Color = QBColor(14) | |
End If | |
Next J | |
Next I | |
End Sub |
沒有留言:
張貼留言