2020年10月24日 星期六

EXCEL VBA FOR 迴圈應用:比較單一重複資料

 文書工作免不了要查核資料有無重複,最簡單的做法是用EXCEL插入表格然後刪除重複,但有時候,就是很難用或是想要知道那幾筆重複了。筆者整理幾個用雙FOR迴圈做比較的簡單例子。

筆者用股票代碼有無重複做例子說明。

image

圖1.股票代號清單。

A行為原始清單,B行為比較清單

例子1:雙層迴圈

先做一個ACTIVEX 命令按鈕,然後增加以下程式碼

Private Sub CommandButton1_Click()

X1 = Application.CountA(Sheets("SHEET1").Range("A:A")) '計算A行數量,並儲存到X1
X2 = Application.CountA(Sheets("SHEET1").Range("B:B")) '計算B行數量,並儲存到X2


    For I = 1 To X1 Step 1 '第一層迴圈,因為用I控制迴圈故稱為I迴圈   


        For J = 1 To X2 Step 1 '第二層迴圈,J迴圈


            If Val(Sheets("SHEET1").Range("A" & I)) =_

 Val(Sheets("SHEET1").Range("B" & J)) Then

 '透過J迴圈去跟I迴圈做比較,因為股票代碼都是數字,單純靠VAL轉換為數字做比較。

                 Sheets("SHEET1").Range("B" & J).Interior.Color = QBColor(6)

 '當有重複時,儲存格背景顯示為黃色

            End If


        Next J


    Next I


End Sub

image

圖.例子1執行結果

例子2:組合INSTR函數

MSDN:INSTR函數說明

先做一個ACTIVEX 命令按鈕,然後增加以下程式碼

Private Sub CommandButton1_Click()

X1 = Application.CountA(Sheets("SHEET1").Range("A:A")) '計算A行數量,並儲存到X1
X2 = Application.CountA(Sheets("SHEET1").Range("B:B")) '計算B行數量,並儲存到X2


    For I = 1 To X1 Step 1 '第一層迴圈,因為用I控制迴圈故稱為I迴圈   


        For J = 1 To X2 Step 1 '第二層迴圈,J迴圈


            If InStr(Sheets("SHEET1").Range("A" & I), _

Sheets("SHEET1").Range("B" & J)) > 0 THEN 

'改成用INSTR的好處是不用管A行有無含有空白,都可以比較,省略比較資料前處理的麻煩。

當比較成立,也就是A資料,含有B資料(被比資料)的關鍵字時,則INSTR回傳1表示含有關鍵字做關鍵存在。當然還有更嚴謹的作法,如增加字串長度確保等。

Sheets("SHEET1").Range("B" & J).Interior.Color = QBColor(14) 

'當有重複時,儲存格背景顯示為黃色

            End If


        Next J


    Next I


End Sub

沒有留言:

張貼留言

怕怕的利益率:113/06