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

順著上一篇，寫完後除了除了稍微自我滿足外也想到另一個問題：那如果「水果」其實不在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的概念讓它完成「如果有找到的話…如果沒找到的話…」的搜尋條件。

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

With Range("我們想要應用搜尋的範圍")
query = "想要找的欄位名稱"
Set result = .find(query, LookIn:=xlValues)
'xlValues的意思是近似Query的值，若要完全相同請使用xlwhole的Operator
If Not result Is Nothing Then
colName = Split(Cells(, result.Column).Address, "$")(1)
End If
End With

在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 變數
變數 = 你所定義它的內容

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

Dim colFullName '定義ColFullname變數
colFullName = colName & "2" '定義為Colnam第二行之後的內容（因為第一行是標題），而colName是我們剛找到的英文字母

不過，如果我們回去重看上一次我們寫的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 = 在已知格子位置情況下，取得該格的值

於是完整公式改為：

Dim colFullName, nowCellValue '定義ColFullname與nowCellValue變數
colFullName = colName & "2" '定義為Colnam第二行之後的內容（因為第一行是標題）
nowCellValue = "INDIRECT(ADDRESS(ROW(), COLUMN()))" '定義為目前我們Macro正在檢查的那一格子的值
IF(OR(nowCellValue=""APPLE"", nowCellValue=""ORANGE"")," & nowCellValue & ",False)
以上，其實就把這一次的搜尋功能給完成了。
不過同理，我們也可以用定義變數的方式把下面的條件整理乾淨，例如不等於蘋果又不等於橘子的條件。
在第一次的寫法中，我們總把不等於蘋果、不等於橘子都得一直往下延伸在IF的條件句裡，讓整個句子變得冗長而不清楚：
IF(OR(B2=""APPLE"",B2=""ORANGE"",B2=""BANANA"", B2=""各種你想要繼續加的水果條件""), B2,FALSE)
但我們也可以把B2 = "水果" 這個公式定義為『條件』變數。
Dim condition1, condition2 '定義有兩個條件
condition1 = nowCellValue & "=""APPLE"""
'條件1為"APPLE"，因為我們不知道符合名稱的欄位在哪裡，因而我就使用上面定義的『搜尋結果nowCellValue』即可
condition2 = nowCellValue & "=""ORANGE"""
'條件2為"ORANGE"，同理如上
因此Formula變為： IF(OR(" & condition1 & "," & condition2 & ")," & nowCellValue & ",False) 
這樣我們未來若要增加條件的話，則只要在formula中的增加Condition3, condition4 然後把條件的內容繼續放在定義中即可。
以下為加上找尋的功能與整理條件後的完整VBA：（已備註說明）
Sub find_No_Apple_orange()
With Range("1:1") '1:1欄位作為我的搜尋範圍
query = "FRUIT" '需要找的目標為FRUIT
Set result = .find(query, LookIn:=xlValues) '搜尋的結果為與FRUIT近似的內容
If Not result Is Nothing Then '如果沒有的話則結束該項檢查
colName = Split(Cells(, result.Column).Address, "$")(1) '捼果有的則取得該欄位位置的英文字母
End If
End With
Dim colFullName, nowCellValue '定義ColFullname與nowCellValue變數
colFullName = colName & "2" '定義為Colnam第二行之後的內容（因為第一行是標題）
nowCellValue = "INDIRECT(ADDRESS(ROW(), COLUMN()))"
'用Address公式得到現在該位置，且使用INDIRECT得到該位置的值，則未得到正在檢查那格子的值
Dim condition1, condition2 '定義各項條件變數
condition1 = nowCellValue & "=""APPLE""" '條件1為"APPLE"
condition2 = nowCellValue & "=""ORANGE""" '條件2為"ORANGE"
Range(colFullName).Select
Range(Selection, Selection.End(xlDown)).FormatConditions.Delete
'先清除掉該表單內的已存條件與被格式化的表格
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="=IF(OR(" & condition1 & "," & condition2 & ")," & nowCellValue & ",False)"
'Formula的內容將於下方仔細解釋
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
End Sub
'Formula1:="=IF(OR(" & condition1 & "," & condition2 & ")," & nowCellValue & ",False)"
'用條件1與條件2作為OR公式的審視條件，當任何一個條件符合時（用上述例子即為APPLE/ORNAGE）則吐出TRUE值，以此作為IF的Logical_test。若得到TRUE，後面則吐出原本該格的內容 nowCellValue，若當它為FALSE, 則吐出False。
其實上面就把這一次的主題講得差不多了，不過如果你跟強者我室友一樣對於公式那邊不斷地要condition1 & "," 往下加條件感到不耐的話，實際上我們也可以用定義變數的方式把Formula變得好看一點。
我個人其實是第一次寫VBA所以對這不是很敏感，但作為工程師的我室友說如果整理乾淨的話，之後要管理或是給其他人接手都會方便許多。
所以以下就來提及如何把Formula那些condition, if, or 也放進變數定義中吧。
唯一要開刀的只有這一行：
 Formula1:="=IF(OR(" & condition1 & "," & condition2 & ")," & nowCellValue & ",False)"
實際上想像成用代數來解釋就可以了：
剛剛的公式化約到最簡單的話，就會是這樣： IF(OR(A, B),C,False)

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

所以IF的內容是三個元素組成的：篩選條件，若符合條件的值，若不符合條件的值。
而篩選條件則是包含OR的公式，於是定義為：
Dim formula As String
formula = "=IF($F_CONDITION, $TRUE_VALUE, ""$FALSE_VALUE"")"
trueValue = nowCellValue
falseValue = "FALSE"
fCondition = "OR(" & condition1 & "," & condition2 & ")"
然後拿已經上面定義好的各種名字放回我們的formula中，在此用Excel replace的功能。
formula = replace（要改寫的範圍，要被改的值，要拿來取代的內容）
formula = Replace(formula, "$F_CONDITION", fCondition)
formula = Replace(formula, "$TRUE_VALUE", trueValue)
formula = Replace(formula, "$FALSE_VALUE", falseValue)
在這情況下，下面兩者就會完全相同了。
formula = "=IF($F_CONDITION, $TRUE_VALUE, ""$FALSE_VALUE"")" 就會等於
Formula = "=IF(OR(" & condition1 & "," & condition2 & ")," & nowCellValue & ",False)"
然後呢？然後我們以後就只要用Formula這名字就可以告訴Macro它的內容是什麼了，所以在原本的macro中告訴excel我所要篩選的Formula1就等於我這邊提及的formula。
以下為完整版（不特別說明）：
Sub find_No_Apple_orange()
With Range("1:1")
query = "FRUIT"
Set result = .find(query, LookIn:=xlValues)
If Not result Is Nothing Then
colName = Split(Cells(, result.Column).Address, "$")(1)
End If
End With
Dim colFullName, nowCellValue
colFullName = colName & "2"
nowCellValue = "INDIRECT(ADDRESS(ROW(), COLUMN()))"
Dim condition1, condition2
condition1 = nowCellValue & "=""APPLE"""
condition2 = nowCellValue & "=""ORANGE"""
Dim formula As String
formula = "=IF($F_CONDITION, $TRUE_VALUE, ""$FALSE_VALUE"")"
trueValue = nowCellValue
falseValue = "FALSE"
fCondition = "OR(" & condition1 & "," & condition2 & ")"
formula = Replace(formula, "$F_CONDITION", fCondition)
formula = Replace(formula, "$TRUE_VALUE", trueValue)
formula = Replace(formula, "$FALSE_VALUE", falseValue)
Range(colFullName).Select
Range(Selection, Selection.End(xlDown)).FormatConditions.Delete
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:=formula
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
End Sub
以上就是第二篇VBA的筆記，把原本的一個小點子發展到這樣的code也是我所料未及的。不過，總是在嘗試新東西後才體會到原來自己能做到的事比想像的多，作為一個文組學生在看到一片花花綠綠的visual basic language的codes還是很有成就感的。
再次感謝強者我室友的教學與帶領，總是能把程式講得很簡單的他目前也持續打算發揮大愛的精神，為大家免費導讀哈佛大學資工概論課CS50, 有興趣的人歡迎到他的粉絲專頁追蹤最新的開課訊息：https://www.facebook.com/lidemytw/
下一篇（沒錯，還沒結束），讓我們來談談怎麼把寫好的這些code自定義為可以在excel使用的功能吧。