〔EXCEL〕MATCH/INDEX-依比對的位置傳回指定欄位值--h015

【依比對的位置傳回指定欄位值】

又是一個繞口的標題…
這是上一次介紹MATCH的用法後
另一個進階應用

MATCH可以傳回比對到值在指定範圍內的位置
例如:在A10:A19(共10個欄位)，在A15找到，則傳回6(因為是第6個欄位找到)
請參閱 〔EXCEL〕MATCH-取得某值在表格中的順位--h014

我們可以再組合INDEX這個函數，可以取得該欄位內的值~
→先找到位置，再依其位置把值取回來

=INDEX( 範圍 , 第幾列 , [第幾欄，預設為0] )
=INDEX( A10:A19 , 6 ) ←回傳A15的值
=INDEX( A10:A19 , MATCH("章老師", A10:A19 , 0) )
這個例子，我們在A10:A19間找尋"章老師"…結果在A15找到
例用MATCH回傳的6，用INDEX的方式，在A10:A19間，取第6列，同一欄的資料。回傳的值就是「章老師」

好了，雖然我一直很想簡單講幾句就把它講完~但有時也很困難~

以下給各位一個我實務上的應用做參考

在CE19，我想抓C22~C25(也就是Q1~Q4目前累計的EPS)
因為EPS數據是自動帶進來的…不確定目前已取得第幾季的數據，且筆數很多，不可能每次自己去改取資料的位置。
但可以確認的是還沒有入的數據，資料內容為"-"

因此我可以這麼做
1.先用MATCH("-",CE22:CE25,0)，傳回第一筆找到的-，回傳值為4
2.再用INDEX(CE22:CE25,MATCH("-",CE22:CE25,0)-1)，取回前一列的值(記得-1)，回傳值為1.92
這樣的組合就可以讓數據的變化是依來源資料自動更新了唷~

實務上有這樣的取值需求時，就再過來研究一下囉!


相關課程：
〔EXCEL〕MATCH-取得某值在表格中的順位--h014

章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔EXCEL〕MATCH-取得某值在表格中的順位--h014

【取得某值在表格中排序的位置】

MATCH這個函數雖不算常用，但偶爾還是有需要它的時候~
它最主要的作用，是可以『比對』指定的值是否在另一表格中，並回傳找到第一筆的位置(順序)…

我刻意用『比對』這樣的敘述來說明這個函數的用法…是否有學員覺得有些似曾相識??

~~函數功能是明確的，但使用方式隨人彈性運用~~

有沒有人覺得這樣的說法跟Vlookup / Hlookup有些些像…
(有興趣的學員再去複習一下囉)

=MATCH(比對值, 比對範圍, 比對方式)
=MATCH("王小明", A2:A100 , 0)
在A2:A100的範圍內找"王小明"，並傳回在第幾個順位找到
若在A2找到，範圍內第一格，回傳1
若在A4找到，範圍內第三格，回傳3…以此類推~

↑↑↑所以，各位可以怎麼應用??
我最近拿來做"排名"的回傳~~

例如上圖Q欄(Q22~Q26，共有5類排名)…
我有另外5張表，分別依據不同類別的金額排序(也就是有了排名)
我不需要真的在表上加上排名的欄位，再用Vlookup把欄位資料帶回來…
只要直接在該範圍內比對它的位置，回傳而來的就是排名囉~

函數參數補述：
比對方式(對此不理解的，再到小講堂來找我囉)
1：(預設)，找小於或等於的最大值
0：需完全一致
-1：找大於或等於的最小值

相關課程：


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕ADO-元件引用項目--v040

【使用資料庫連線需加入的引用】

Microsoft ActiveX Data Objects 6.1 Library
Microsoft ActiveX Data Recordset 6.0 Library

若不引用上述項目，在宣告元件時，請使用CreateObject的方式




相關課程：


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕ADODB.Connection-建立資料庫連線--v039

【建立一個資料庫MSSQL連線】

Public Function SCE_GetConn() As ADODB.Connection
    Dim Conn As ADODB.Connection
    Dim sConnect As String
    Dim strSqlInstance As String
    Dim strSqlDB As String
    Dim strSqlUser As String
    Dim strSqlPWD As String
    ' strSqlInstance = "SERVER_NAME\INSTANCE"
    ' 如果伺服器只有裝一次 SQL，就只要輸入伺服器名稱
    strSqlInstance = "."
    strSqlDB = "DBName"
    strSqlUser = "sa"
    strSqlPWD = "sqlpassword"
    sConnect = "Provider=SQLOLEDB"
    sConnect = sConnect & ";DATA SOURCE=" & strSqlInstance & ";Initial Catalog=" & strSqlDB
   ' 如果把 User ID=...;Password=... 改成 "Integrated Security=sspi;"，認證方式就變成 AD 登入帳號
    sConnect = sConnect & ";User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"
    Set Conn = New ADODB.Connection
    Conn.ConnectionString = sConnect
 
    Set SCE_GetConn = Conn
End Function



相關課程：


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔EXCEL〕Countif-用顏色自動標示重覆的資料--h013

【運用格式化設定自動標示重覆的資料】

其實這課程先前都已上過，所以今天只是再舉個實際範例來實現這樣的作法~

找出資料是否重覆的方法有很多
一般很常見的使用比對的方式，例如：Vlookup、Match...
只要能達到目的的都是好方法…

不過方法很多也不用每種都記住啦~
練得一招走天下，用得精也是威力無窮...

有鑑於我的腦容量不是很夠用，通常找到更簡單易記的方式，我就會把它變成一種標準用法，遇到就套用…成為不用腦的自然反應…

所以現在要標示出重覆的資料…我都直接用
Countif + 格式化設定的條件
來讓重覆的資料自動變色…達到一目了然的目的…

這個資料的需求，是想要知道「紅框處」有哪些代碼在「藍框處」有出現
(哪幾家是連三買股價卻不漲的資料)

因此可以直接在V6欄增加一個「格式化設定的條件」如下

=COUNTIF($G:$G,$V6)=1
G欄值不會重覆，因此只要設定=1即可。
若G欄會有重覆值，請設定為>=1。

設定完後，再修改其『套用範圍』即完成囉!

相關課程可以參閱以下的連結囉!

相關課程：
〔EXCEL〕如何Highlight重覆資料--h005


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕ProtectContents-取得工作表保護狀態--v038

【如何取得工作表保護(鎖定)狀態】

Private Sub cmdLock_Click()
    '鎖定或解鎖
    If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect    '解鎖
        Me.cmdLock.Caption = "鎖定"
    Else
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
            AllowFormattingRows:=True        '鎖定
        Me.cmdLock.Caption = "解鎖"
    End If
End Sub

相關課程：


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕StartUpPosition-指定表單開啟時的位置--v037

【指定表單開啟時的位置】

Private Sub UserForm_Initialize()
    '右下角
    Me.StartUpPosition = 0
    Me.Top = Application.Top + Application.Height - Me.Height - 20
    Me.Left = Application.Left + Application.Width - Me.Width - 20
End Sub

設定	值	描述
手動	0	指定沒有初始設定。
CenterOwner	1	UserForm所屬的項目上的管理中心。
CenterScreen	2	管理中心在整個螢幕。
WindowsDefault	3	螢幕的左上角位置。

相關課程：


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕FileDialog-開啟視窗選取資料夾--v036

【開啟視窗選取資料夾顯示路徑】

    With Application.FileDialog(msoFileDialogFolderPicker) '開啟選取檔案視窗
      .InitialFileName = ActiveWorkbook.Path & "\" '預設為目前檔案所在位置
      .AllowMultiSelect = False '只能選取單一資料夾
      If .Show <> 0 Then '如果有選到資料夾
           MsgBox .SelectedItems(1) '顯示資料夾路徑
      End If
    End With

相關課程：


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔EXCEL〕SWITCH-依比對值回傳對應值--h012

【依比對值回傳對應值】

老實說，上面的敘述連我都很難理解…
直接用以下的範例來說明好了…

如果你有份資料，是這麼多的數字…不是常用的人是不是一眼很雖以計算這到底是多少$$
所以我想用C2的單位切換，讓資本額跟著同步變動

例如，設定有上述幾個單位可做切換(元/千元/萬元/百萬/億元)
〔下拉式選單(資料驗証)之後再開課囉，今天的重點是SWITCH〕

=SWITCH(比對的值, 對應值1, 對應回傳值1, 對應值2, 對應回傳值2, ...)
依此類推可以比對多個對應值…

以上述例子，我希望依據「單位C2」的值回傳一個除數，以便將資本額去除該數得到我要的結果…
元→資本額/1
千元→資本額/1000
萬元→資本額/10000
百萬→資本額/1000000
億元→資本額/100000000

利用SWITCH回傳比對到的值
=SWITCH(C2,"元",1,"千元",1000,"萬元",10000,"百萬",1000000,"億元",100000000)

如此一來，就可在資本額欄位下以下的公式
=56656192040/SWITCH($C$2,"元",1,"千元",1000,"萬元",10000,"百萬",1000000,"億元",100000000)

同理，請把市值當作業吧!










相關課程：


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕FollowHyperlink-開啟非Excel檔案--v034

【以超連結開啟方式打開檔案】

ThisWorkbook.FollowHyperlink  "file:///C:\ScenicSchool\資料夾或檔案"
ThisWorkbook.FollowHyperlink  "file:///" & ActiveWorkbook.Path & "\資料夾或檔案" 


在儲存格上，可以輕易的加入超連結，以方便開啟檔案、指定工作表、或網頁…
按右鍵加入超連結即可…
甚或使用vba的方式把超連結加入(參考下方「相關課程」)

但以上的方式，超連結一旦加入…它的路徑就是固定的不會任意被改變~
路徑固定有什麼影響呢?
1. 對網址而言沒有影響
2. 對同一張表內的指定工作表也沒有影響…
3. 對連結其它的檔案(包括Excel、PDF等等)，倘若位置改變了…那就有可能找不到檔案~

例如我使用雲端硬碟去同步一些共用資料，但在不同的電腦，雲端硬碟同步的路徑不同…
如此狀況，就會造成我在A電腦手動加入的連結，在B電腦打開時因為路徑差異造成連結不能使用…
索性使用以超連結方式去動態開啟檔案~

雖然也可以使用Shell的方式來開啟檔案，但會遇到路徑太長被截斷的問題…
總之，跨時代了就用新方法來處理吧~


相關課程：
〔VBA〕Hyperlinks-儲存格加入超連結--v015
〔VBA〕Hyperlinks-清除的方式--v018


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕Shapes.Delete-一次刪除所有圖片/圖形--v035

【一次刪除工作表內所有圖片/圖形】

Sub DeleteAllShapes()
    
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        shp.Delete
    Next shp

End Sub

相關課程：


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔EXCEL〕DATE-日期加減N年或N月的方法--h011

【日期加減N年N月】

「日期」，在系統的世界裡，其實是一個「數字」…
例如2019/9/20，在Excel中將格式設定為「數值」…
你會發現它就變成43728…
而這個，才是它真實的面目…日期格式只是它美麗的外衣而已~(地區化的分別)

即然日期，是個「數字」，所以你試試將上述數字+1…
再將格式改為「日期」時，會變成幾月幾日?

由此可知…日期+N天…是非常輕而易舉的事…
直。接。加。即。可。

倘若你想加N月呢?  (因為有大小月呀~到底該加多少天呢???)

這時，你可以善用DATE這個函數~

=DATE( 年 , 月 , 日)
=DATE( 2019 , 9 , 20 )             → 2019/9/20
=DATE( 2019 , 9+1 , 20)          → 2019/10/20
=DATE( 2019 , 9+5 , 20)          → 2020/2/20

利用這個函數，年份可是會自動幫你計算 的唷~

那有沒有人好奇 =DATE( 2019 , 9+5 , 30) 會變成幾月幾日呢?
請自己試一試唷~


再搭配以下相關日期函數，取得某欄位日期的年、月、日 (上圖範例)
=YEAR(A5)                              →取得指定日期的年 2019
=MONTH(A5)                          →取得指定日期的月 8
=DAY(A5)                                →取得指定日期的日 1

是否就可以在DATE中個別的年、月、日去做加減呢???

上圖範例需求，在建立資料時，最近一期為2019/08，要增建前面的歷史資料，用每個期別減1個月的方式可快速複製從下拉，不然幾年的資料要輸入到什麼時候是吧~
===>我真的沒有耐心一筆筆輸入呀~~~


相關課程：無


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔EXCEL〕儲存格樣式-更改預設的「超連結」--h010

【如何更改預設的超連結格式】

當你在儲存格內，加入超連結時，會發現超連結的字型顏色會"自動"改變~
而且當你真的點選連結後，它又會變成另一個顏色~

預設：
超連結→細明體 / 12pt / 藍色
已瀏覽過→紫色


如果這個字型及顏色，和整個版面很不搭時，會不會心裡有圈圈叉叉的產生~
若只是單一兩個，自己調整一下倒也還可接受…
但若是有大量的連結設定呢~~

若你和我一樣有著固執的版面潔癖...那就來動手更改連結的預設值吧~
首先，功能位置在：

「常用」→「儲存格樣式」→找到「超連結」→按右鍵選「修改」

改成你喜歡的顏色/字型/字體大小等等，再套用即可~
(細部設定大家自己試試唷~這裡就不多說了)

不過各位有沒有發現一個好玩的地方…
我們把「超連結」的格式修改了…
那已連過的超連結格式，它也會自動變色…但這裡好像沒那個設定選項耶~

請~請~請隨便點選一個設定好的超連結儲存格，讓它真的變色後…
再按上述路徑，就會發現...多了一個「已瀏覽過」的選項唷~~

哈哈~我也不知為何工程師要這樣"動態"設定…
總之，試試吧~打資料時會比較愉悅喔~

相關課程：無。


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔EXCEL〕自定格式-數字轉換中文大寫(一十一/十一)--h009

【如何將數字轉換成中文大寫(含隱藏設定)】

((先提示，今天的重點在最後一段))


前一篇分享了用小計功能來做分類縮合選單…
上傳了"經書"的範例…(哈~只不過是我自己剛好在整理使用)…
其中，各章節當然是以中文「第一章」「第二章」…來顯示是最恰當的…

但我不斷強調我是一個非常懶的人…輸入3個中文字，不如輸入一個阿拉伯數字…
因此最簡單的方式，就是將章節的欄位格式，修改為「特殊」類型中的中文數字選項…

其中有2個選項可選擇(如上圖)~~~
選擇確定後…再打開儲存格數值設定，選取自訂，會看到真實的設定碼
(依據Excel版本的不同，顯示會有些差異)
[DBNum1][$-zh-TW]General
[DBNUM1][$-404]G/通用格式

請在前後加上"第"xxx"章"…這樣顯示時前後字都不需登打了…
[DBNum1]"第"[$-zh-TW]General"章"
[DBNUM1]"第"[$-404]G/通用格式"章"

好的…以上其實各位簡單試試就可以得到結果…
但…還沒有到我今天要講的隱藏設定…(((鵝~~大驚)))

依目前Excel格式設定選單中，選取的中文數字
若輸入11，顯示會轉換成「一十一」或「壹拾壹」
>>>>>有沒有覺得很不習慣，如果可以把前的面「一/壹」去掉，是不是比較接近想要顯示的方式??

解決方法：
請將404代碼，修改為411即可~~
[DBNum1]"第"[$-ja-JP]General"章"
[DBNUM1]"第"[$-411]G/通用格式"章"

完美~~試試吧~


<<參考資料>>
代碼 DBNUM1 DBNUM2 DBNUM3
404 一百二十三 壹佰貳拾參 １百２十３
411 百二十三 壱百弐拾參 百２十３
412 一百二十三 壹百貳拾參   百２十３
804 一百二十三 壹佰貳拾叄  １百２十３
10000 一百二十三 壹佰貳拾叄 １百２十３
10404 一百二十三 壹佰貳拾參 １百２十３
10411 百二十三 壱百弐拾參 百２十３
10412 一百二十三 壹百貳拾參 百２十３
10804 一百二十三 壹佰貳拾叄 １百２十３
20000 一百二十三 壹佰貳拾叄 １百２十３
20404 一百二十三 壹佰貳拾參 １百２十３
20411 百二十三 壱百弐拾參 百２十３
20412 一百二十三 壹百貳拾參 百２十３
20804 一百二十三 壹佰貳拾叄 １百２十３
11000000 ፩፪፫ ፩፪፫ ፩፪፫
27000000 일백이십삼 일백이십삼 일백이십삼








相關課程：


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕FileDialog-開啟視窗選取檔案--v033

【利用FileDialog產生檔案選取視窗】

With Application.FileDialog(msoFileDialogFilePicker) '開啟選取檔案視窗 
      .InitialFileName = ActiveWorkbook.Path & "\*.*" '預設為目前檔案所在位置 
      .AllowMultiSelect = False '只能選取單一檔案 
      If .Show <> 0 Then '如果有選到檔案 
           Dim sFileName As String 
           sFileName = Dir(.SelectedItems(1)) '取得檔名 
      End If 
End With


相關課程：


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔EXCEL〕資料小計-資料分類好幫手--h008

【運用小計功能來分類資料】

不知有沒有學員使用Excel來紀錄分類的資料?
我個人是蠻常使用的，不管是工作上或私人筆記。
例如程式版本清表、資料庫欄位總表…或是網站連結、經書筆記等等…

在整理資料時，希望能以「分類」方式來彙整…
並且有展開收合的功能…
這樣在瀏覽資料時，不但能快速一覽全貌，並且可以點選方式直接檢視要瀏覽的資料內容…
非常方便~~~

運用Excel資料小計的功能，就能達到這個效果唷…
當然，小計功能主要是為了拿來做些分類計算(例如筆數、總計等等)…
但運用隨人發揮，利用其收合分類的功能，立馬效率提昇~

在Excel「資料」選項中，有「群組」及「小計」功能…
我個人偏愛使用小計的功能，主要原因在於…

• 小計的收合點選按鈕在上方，而群組在下方(各位有興趣可以自己試試)…
• 可方便迅速加上統計的功能(例如圖二的數字為subtotal自動計算下方有幾筆資料)

產生小計格式的方式，在這裡我不詳述步驟…
因為最簡單的方式，就是有幾筆資料後，選取按小計…先讓結構產生出來後，再依自己使用方式調整及編排即可…
直接…試了…就知道了…

唯一注意的是…小計最多只有3層的結構，同時只有列(直向)的分類收合。
但群組則沒有限制層數(沒試過極限)，可針對欄及列來進行分類。

對於沒有耐心的我…分類3層其實已經夠了，太多層反而眼花撩亂，不如分Sheet來管理…
點3層也夠了…再多操作上就不輕鬆了…

大家可以參考應用看看囉~


範例檔下載

相關課程：無


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕Desktop-取得桌面路徑--v032

【取得目前使用者桌面路徑】

MsgBox "桌面路徑：" & CreateObject("WScript.Shell").SpecialFolders("Desktop")

相關課程：無。

章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕Close-關閉活頁簿--v031

【關閉指定活頁簿】

ActiveWorkbook.Close
Workbooks("目前開啟的檔名.xlsx").Close


若要避免跳出存檔提示視窗，可暫時將系統提示訊息關閉(檔案有被修改將不會儲存)
Application.DisplayAlerts = False
Workbooks("目前開啟的檔名.xlsx").Close
Application.DisplayAlerts = True


參考資料：
https://docs.microsoft.com/zh-tw/office/vba/api/excel.workbooks.close

相關課程：
〔VBA〕Open-開啟活頁簿--v030
〔VBA〕DisplayAlerts-存檔時關閉系統提示--v021


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕Open-開啟活頁簿--v030

【開啟指定活頁簿】

Workbooks.Open Filename:="檔案完整路徑.xlsx", UpdateLinks:=0

• UpdtaeLinks:=0 代表檔案開啟時不會更新外部參照
• ReadOnly:=true 代表以唯讀檔案開啟，預設為False

參考資料：
https://docs.microsoft.com/zh-tw/office/vba/api/excel.workbooks.open

相關課程：
〔VBA〕Close-關閉活頁簿--v031


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕範例：進階版多選動態螢光棒(列變色)--v029

【點選儲存格，則該列底色反黃變色-多選版】

繼先前針對單選儲存格，底色動態變色的範例應用後
(〔VBA〕範例：動態螢光棒(列變色)的作法[格式化的條件]--v010)

我們再進一步針對多選列，來進行底色變色的"實驗吧"

作法如同單選變色的方式，只是增加了選取區域(Areas)的判斷
針對每個選取列，來增設「設定格式化的條件」

語法：
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    Range("C4:W33").FormatConditions.Delete       '將C4:W33內格式化條件的設定刪除
    Dim tRange As Range, tRow As Range
    For Each tRange In Target.Areas
        For Each tRow In tRange.Rows
            '只針對範圍設定在C4~W33
            If tRow.Row > 3 And tRow.Row < 34 Then
                '設定該區的格式化條件
                With tRow.EntireRow.Range("C1:W1").FormatConditions
                    .Add xlExpression, , "TRUE"
                    .Item(1).Interior.ColorIndex = Int(36)
                    .Item(1).StopIfTrue = False
                End With
            End If
        Next
    Next
    Set tRange = Nothing: Set tRow = Nothing
End Sub

補充：
With tRow.EntireRow.Range("C1:C1").FormatConditions
        .Add xlExpression, , "TRUE"
        With .Item(1).Font                             '字型
            .Color = -11489280
            .Bold = True
            .Italic = False
        End With
        With .Item(1).Borders(xlBottom)      '下框線
            .LineStyle = xlContinuous
            .Color = -16776961
            .Weight = xlThin
        End With
        With .Item(1).Interior                         '填滿
            .PatternColorIndex = xlAutomatic
            .Color = 65535
        End With
        .Item(1).StopIfTrue = False
End With


相關課程：
〔VBA〕範例：動態螢光棒(列變色)的作法[格式化的條件]--v010

章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕Unload Form-關閉表單--v028

【關閉自定義表單】

Unload Form

or

Unload Me

'在表單程式碼程序內Unload Me則卸載執行物件

章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕Form.Hide-隱藏表單--v027

【隱藏自定義表單】

Form.Hide

'表單隱藏，且為強制回應
'表單上元件內容不會因此清空或卸載

章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔EXCEL〕範例-將姓名中間字改成○--h007

【如何將姓名中間字改成○】

今天一位學員問我，怎麼快速將大量姓名中間的字換成○?
其實解決問題的方法很多，越白話越簡單越好記…

以下列舉2個方式來解決這個問題：<可使用第2個方式，但不能不熟悉第1個方式，哈哈>

1. 用左邊第1個字 + ○ + 右邊第1個字
    (這是最直覺最白話的方式)

= LEFT(B3,1) & "○" & RIGHT(B3,1)    '這叫直譯式寫法，哈哈!

但若名字有4個(甚至更多)或只有2個字呢?
可以加入長度的判斷來決定

= LEFT(B3,1) & IF(LEN(B3)>3,"○",LEFT("○○○○",LEN(B3)-2)) & IF(LEN(B3)>2,RIGHT(B3,1),"")

其中
．IF(LEN(B3)>3,"○",LEFT("○○○○",LEN(B3)-2))
→若姓名長度>3(四個字以上)，就換長度-2個○(目前最多是6個字)
．IF(LEN(B3)>2,RIGHT(B3,1),"")
→若姓名長度>2，就放入姓名最後一個字(也就是說2個字的，就不放最後一個字)


2. 用置換的方式，將中間的字換成○
利用REPLACE函數，將指定位置的字元替換成○

= REPLACE(原字串 , 從第幾碼開始替換 , 替換幾個字元 , 替換的文字 )

= REPLACE(B3,2,1,"○")

同樣的，針對姓名2個字或4個字以上的增加判斷處理
= REPLACE( B3 , 2 , IF(LEN(B3)>3,LEN(B3)-2,1) , IF(LEN(B3)<3,"○",LEFT("○○○○",LEN(B3)-2)) )
其中
．IF(LEN(B3)>3,LEN(B3)-2,1)
→若姓名長度>3，則替換掉長度-2的字元(4個字替換掉中間2個字)
．IF(LEN(B3)<3,"○",LEFT("○○○○",LEN(B3)-2))
→若長度<3，僅替換1個○，否則替換長度-2個○(4個字中間替換成2個○)


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/
※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕FileSystemObject-檢查檔案是否存在 --v026

【使用FileSystemObject判定路徑下檔案是否存在】

Dim fs as Object
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.fileexists( 完整檔案路徑含副檔名 ) = True Then
    '檔案存在
Else
    '檔案不存在
End If
Set fs = Nothing

相同功能：
〔VBA〕Dir-檢查檔案是否存在 --v001
〔VBA〕FileSystemObject-檢查檔案是否存在 --v026

章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕範例：Outlook批次寄發Email(2/2)--v025

【如何替Email掛上附件】

前一篇我們已知道如何產生Email郵件
〔VBA〕範例：Outlook批次寄發Email(1/2)--v024

若要自動掛上附件，要怎麼處理呢?

'替Email掛上附件===============================================
Dim objOL As Object, objMail As Object
Set objOL = CreateObject("Outlook.Application")   '定義一個outlook物件
Set objMail = objOL.CreateItem(olMailItem)           '定義一個Mail項目

Dim myAttachments as Object
myAttachments = objMail.Attachments
myAttachments.Add 附件的完整路徑
'↑這樣該附件就會被加入這份Mail中囉~
'若要加多份，只要重覆myAttachments.Add即可

Set myAttachments = Nothing
Set objMail = Nothing
Set objOL = Nothing

完整課程：
〔VBA〕範例：Outlook批次寄發Email(1/2)--v024
〔VBA〕範例：Outlook批次寄發Email(2/2)--v025

章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕範例：Outlook批次寄發Email(1/2)--v024

【利用預設郵件批次產生Email寄發】

這個功能在工作領域的應用上，可以發揮非常大的作用…
有需要的學員，不妨試一試…

舉例來說：
採購人員每天發單給供應商，可以統整張訂購表
可以利用Excel，從清冊直接轉成一張張的採購單
並另存成PDF檔，自動掛到Email附件中
包含內文的抬頭，內容都能自動生成

細節不多說了~
本篇主要重點為產生Email的那段語法

'寄送Email===============================================
Dim objOL As Object, objMail As Object
Set objOL = CreateObject("Outlook.Application")   '定義一個outlook物件
Set objMail = objOL.CreateItem(olMailItem)           '定義一個Mail項目

'設定Mail項目的相關屬性
With objMail
    .To = 收件者
    .Cc = 副本
    .Bcc = 密件副本
    .Subject = 主旨
    .Body = 信件內容
    .Display        '顯示郵件
End With

↓***以下這段，我沒試過。因為不建議直接送出。重要文件還是看過再送~
'SendKeys "%s", True ' 自動按ALT+S以送出郵件

Set objMail = Nothing
Set objOL = Nothing

完整課程：
〔VBA〕範例：Outlook批次寄發Email(1/2)--v024
〔VBA〕範例：Outlook批次寄發Email(2/2)--v025

章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕FileSystemObject-資料夾不存在則新增--v023

【判斷資料夾是否存在，若資料夾不存在，則新增】

Dim fs as Object
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FolderExists(資料夾完整路徑) = False Then
    '資料夾不存在
    fs.CreateFolder (資料夾完整路徑)
End If
Set fs = Nothing

記得要釋放fs


章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔VBA〕FileSystemObject-檢查資料夾是否存在--v022

【使用FileSystemObject函數判定路徑下資料夾是否存在】

Dim fs as Object
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FolderExists( 資料夾完整路徑 ) = True Then
    '資料夾存在
Else
    '資料夾不存在
End If
Set fs = Nothing
記得要釋放fs

相同功能：
〔VBA〕Dir-檢查資料夾是否存在--v002
〔VBA〕FileSystemObject-檢查檔案是否存在 --v026

章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :

〔EXCEL〕篩選-用顏色來篩選--h006

【用顏色來篩選】

其實就算再經常使用Excel，也容易因過度習慣，而忽略了就在眼前的功能。

不用懷疑，我就是。

這個功能是為了解決一個學員的問題，而想到「不能用顏色篩選嗎??」

點進去看，耶~~~還真的有耶…

有時解法就在眼前，所以程式講的是邏輯…

邏輯講的是思考的順序和方法…

(慢慢要離題了)

篩選功能，就不用多說了…有機會如圖試試即可~

結果如下

章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

關聯篇：〔EXCEL〕如何Highlight重覆資料--h005

Search Post

Label :

〔EXCEL〕如何Highlight重覆資料--h005

【設定格式化的條件，配合COUNTIF標示重覆的資料】

上次有利用COUNTIF來帶出重覆資料的筆數

〔EXCEL〕COUNTIF抓出重覆的資料--h004

這次加入格式化的條件，來些變型的應用…

之前其實有在小講堂FB分享過，現在再次拿出來複習一下唷~

以本範例說明，希望將有重覆資料的產品自動顯示底色(產品B有重覆)

  

第一步：串組判斷產品重覆的公式(以C4欄為例-產品H)

=COUNTIF( $C:$C , $C4 )>1

口訣：計算C欄所有等於C4值的筆數，也就是計算所有產品為H的筆數

若>1，則為”真”，就是要設底色~~

第二步：開始「設定格式化的條件」

1.     點選「設定格式化的條件」功能。

2.     選擇「使用公式來決定要格式化哪些儲存格」。

3.     在公式欄輸入第一步的公式 「=COUNTIF( $C:$C , $C4 )>1」，記得前面要有=喔~

4.     設定「格式」，填滿底色。完成後確認。

5.     再次點選「設定格式化的條件」功能。

6.     將套用範圍擴及到整個C欄「=$C:$C」。

這樣就大功告成囉~~

章老師的電腦小講堂 https://www.facebook.com/ScenicSchool/

※※ 本區做為經常使用之程式碼複製區，提問請至小講堂唷 ※※

Search Post

Label :