[筆記] Excel VBA-2:如何自動尋找相關欄位並應用條件格式化

Aki
Aki in akichy
Jul 8, 2017 · 15 min read

順著上一篇,寫完後除了除了稍微自我滿足外也想到另一個問題:那如果「水果」其實不在B欄位怎麼辦? 如果今天我每次開試算表,水果的欄位都不是固定的又該怎麼處理呢?

讓我們再拿上一篇文章的截圖作為範例。

假設你今天打開的試算表B欄位是水果,但明天你同事給你的報表,變成C欄位是水果 — — 你可能不會想要每次看到是哪一個欄位是水果之後再手動去改你的VBA macro。把 Range(“B2”).Select 改為不同的 Range(“你想要的欄位”).Select 有用但感覺每次都要編輯Macro有些麻煩。

所以我們試想有無可能寫一個VBA script先自己去搜尋符合條件的名字後,再來應用我們的格式化篩選呢?特別是在有非常多欄位的試算表情況下,你可能得手動先去找A1-Z1到底哪一個符合「水果」的欄位名稱,如此的做法非常不效率。因而讓我們來稍微改變上一次的Macro吧。

邏輯其實很簡單,從欄位的第一列從左到右先去篩選是否有我們想要找的欄位名字。然後找到有符合的名字欄位後,再以該直行由上到下的方式來應用我們上次所完成的Macro應用。

  • 找尋的Function

使用.find()的公式即可完成,並不會太困難。但值得注意的部分是:我們找到這欄位後希望它可以告訴我們是A, B, C的欄位英文字母,這樣我們才能把它傳給下部分的Script去跑條件式格式化。我用If, Then and else的概念讓它完成「如果有找到的話…如果沒找到的話…」的搜尋條件。

========說明版=========

在Then後面,我們加上了這則定義來讓找尋的功能讓我們得到欄位名稱,這看起來複雜但其實不然。 colName = Split(Cells(, result.Column).Address, "$")(1)

實際上就是以搜尋的結果(Result)取得它所在的位置(Address), 但我們並不需要它完整的位置地址(例如A1, B3 etc),而只需要欄位的英文字母。因此使用Cells跟Split功能再度把地址拆開來,取得第一個字母就好。於是上面那一小段的公式翻譯如下:

在範圍中,我先定義出想要找的東西為何,以此定義的目標去找出與它相似的值(請注意因為我們使用了xlValues),如果沒有結果的話則結束搜尋;但若有結果,在此增加一個新東西叫做ColName(欄位名稱)是由找到該格的位置後所拆下的第一個英文字母。

接著,我們需要來「使用」我們拿到的英文字母。這裡開始想法與Macro寫法變得有些複雜,基本上是由於Excel本身公式的限制導致我必須得繞路得到我想要的值,但下述例子可以用代數作為範例先理解的話,就會比較容易了解脈絡。

拿到了B這字母後要幹嘛呢?

第一件事情是告訴下面的Macro,B2以下(B2:B)就是我們要應用的條件格式化的範圍了。 在Macro中若要使用變數的功能,則需要先定義變數:

Dim 變數 變數 = 你所定義它的內容

這樣你才能使用你定義的變數在下面的公式或是功能中而不用每次都重新把整串定義寫出來。於是完整版長得如下面所示,應該蠻好理解的:

不過,如果我們回去重看上一次我們寫的Macro, 可能就會發現這個變數有點小問題:

我們應用條件格式化格式化的公式是:

(上次的公式) IF(OR(B2=””APPLE””,B2=””ORANGE””), B2,FALSE) 1. 先跑OR的功能:若 B2=APPLE 或 B2=ORANGE 任何一項條件完成則顯示TRUE 2. 若(1)顯示為TRUE的話,那麼顯示B2那一格的內容 3. 若(1)顯示為FALSE的話,那麼顯示為FALSE

(現在我們的寫法) colFullName = colName & “2” IF(OR(colFullName=””APPLE””,colFullName=””ORANGE””), colFullName,FALSE)

我們的ColFullname是一個格子,所指的是 colName & “2”,但我們所想要篩選的條件並非把B欄位的每格都拿來跟B2比,另外,如果我們直接用colFullName來取代上次寫的B2的話,那麼當條件顯示為TRUE時,他所吐出的變成B2的格子而不是原本自己的那格內容。

於是,我們需要再定義一個「現在正在被檢查的格子」的變數,姑且稱它為nowCellValue好了。

它本身其實沒什麼內容,它主要是告訴我:我的macro正在檢查第幾行、第幾列的格子然後那一格的值為何。這樣我們只要拿nowCellValue來跟我們的篩選條件比較,若為真的情況下則持續保持nowCellValue的內容即可。

nowCellValue = "INDIRECT(ADDRESS(ROW(), COLUMN()))"

  • ADDRESS = 取得所要格子的位置
  • INDIRECT = 在已知格子位置情況下,取得該格的值

於是完整公式改為:

  • A 跟 B 是我們的篩選公式
  • C 是原本格子的值,在為TRUE的時候要繫留著的,現在是 nowCellValue 變數的內容
  • FALSE就是如果錯誤的話的值,這邊保持不變

akichy

#Notes of everything

Aki

Written by

Aki

Product Specialist @Facebook APAC | Tech products management/ development enthusiast| 人生目標是「有知有覺地過每一天」

akichy

akichy

#Notes of everything