VB常用技巧教材

VBA常用技巧

目錄

VBA常用技巧1

第1章Range（單元格）對象4

技巧1單元格的引用方法4

1-1使用Range屬性4

1-2使用Cells屬性5

1-3使用快捷記號5

1-4使用Offset屬性6

1-5使用Resize屬性7

1-6使用Union方法8

1-7使用UsedRange屬性8

1-8使用CurrentRegion屬性9

技巧2選定單元格區(qū)域的方法9

2-1使用Select方法9

2-2使用Activate方法10

2-3使用Goto方法11

技巧3獲得指定行、列中的最后一個非空單元格11

技巧4定位單元格14

技巧5查找單元格15

5-1使用Find方法15

5-2使用Like運(yùn)算符19

技巧6替換單元格內(nèi)字符串20

技巧7復(fù)制單元格區(qū)域21

技巧8僅復(fù)制數(shù)值到另一區(qū)域24

8-1使用選擇性粘貼24

8-2直接賦值的方法25

技巧9單元格自動進(jìn)入編輯狀態(tài)26

技巧10禁用單元格拖放功能26

技巧11單元格格式操作27

11-1單元格字體格式設(shè)置27

11-2設(shè)置單元格內(nèi)部格式29

11-3為單元格區(qū)域添加邊框30

11-4靈活設(shè)置單元格的行高列寬32

技巧12單元格中的數(shù)據(jù)有效性33

12-1在單元格中建立數(shù)據(jù)有效性33

12-2判斷單元格是否存在數(shù)據(jù)有效性34

12-3動態(tài)的數(shù)據(jù)有效性35

12-4自動展開數(shù)據(jù)有效性下拉列表37

技巧13單元格中的公式38

1

13-1在單元格中寫入公式38

13-2檢查單元格是否含有公式39

13-3判斷單元格公式是否存在錯誤40

13-4取得單元格中公式的引用單元格41

13-5將單元格中的公式轉(zhuǎn)換為數(shù)值42

技巧14單元格中的批注43

14-1判斷單元格是否存在批注43

14-2為單元格添加批注44

14-3刪除單元格中的批注45

技巧15合并單元格操作46

15-1判斷單元格區(qū)域是否存在合并單元格46

15-2合并單元格時連接每個單元格的文本47

15-3合并內(nèi)容相同的連續(xù)單元格48

15-4取消合并單元格時在每個單元格中保留內(nèi)容50

技巧16高亮顯示單元格區(qū)域51

技巧17雙擊被保護(hù)單元格時不顯示提示消息框52

技巧18重新計算工作表指定區(qū)域54

技巧19錄入數(shù)據(jù)后單元格自動保護(hù)54

技巧20工作表事件Target參數(shù)的使用方法56

20-1使用單元格的Address屬性56

20-2使用Column屬性和Row屬性57

20-3使用Intersect方法57

第2章Worksheet（工作表）對象59

技巧21引用工作表的方式59

21-1使用工作表的名稱59

21-2使用工作表的索引號59

21-3使用工作表的代碼名稱60

21-4使用ActiveSheet屬性引用活動工作表60

技巧22選擇工作表的方法61

技巧23遍歷工作表的方法62

23-1使用For...Next語句62

23-2使用ForEach…Next語句64

技巧24在工作表中上下翻頁65

技巧25工作表的添加與刪除66

技巧26禁止刪除指定工作表70

技巧27自動建立工作表目錄72

技巧28工作表的深度隱藏74

技巧29防止更改工作表的名稱76

技巧30工作表中一次插入多行77

技巧31刪除工作表中的空行78

技巧32刪除工作表的重復(fù)行80

技巧33定位刪除特定內(nèi)容所在的行82

技巧34判斷是否選中整行83

技巧35限制工作表的滾動區(qū)域84

2

技巧36復(fù)制自動篩選后的數(shù)據(jù)區(qū)域85

技巧37使用高級篩選獲得不重復(fù)記錄87

技巧38工作表的保護(hù)與解除保護(hù)88

技巧39奇偶頁打印91

第3章Wordbook（工作簿）對象93

技巧40工作簿的引用方法93

40-1使用工作簿的名稱93

40-2使用工作簿的索引號93

40-3使用ThisWorkbook94

40-4使用ActiveWorkbook95

技巧41新建工作簿文件95

技巧42打開指定的工作簿97

技巧43判斷指定工作簿是否打開100

43-1遍歷Workbooks集合方法100

43-2錯誤處理方法101

技巧44禁用宏則關(guān)閉工作簿101

技巧45關(guān)閉工作簿不顯示保存對話框105

45-1使用Close方法關(guān)閉工作簿105

45-2單擊工作簿關(guān)閉按鈕關(guān)閉工作簿107

技巧46禁用工作簿的關(guān)閉按鈕107

技巧47保存工作簿的方法109

47-1使用Save方法109

47-2直接保存為另一文件名109

47-3保存工作簿副本109

技巧48保存指定工作表為工作簿文件110

技巧49打印預(yù)覽時不觸發(fā)事件111

技巧50設(shè)置工作簿文檔屬性信息114

技巧51不打開工作簿取得其他工作簿數(shù)據(jù)115

51-1使用公式115

51-2使用GetObject函數(shù)115

51-3隱藏Application對象116

51-4使用ExecuteExcel4Maero方法117

51-5使用SQL連接119

技巧52返回窗口的可視區(qū)域地址120

3

第1章Range（單元格）對象

Range對象是Excel應(yīng)用程序中最常用的對象，一個Range對象代表一個單元格、一

行、一列、包含一個或者更多單元格區(qū)域（可以是連續(xù)的單元格，也可以是不連續(xù)的單元格）

中選定的單元格，甚至是多個工作表上的一組單元格，在操作Excel內(nèi)的任何區(qū)域之前都

需要將其表示為一個Range對象，然后使用該Range對象的方法和屬性。

技巧1單元格的引用方法

在VBA中經(jīng)常需要引用單元格或單元格區(qū)域區(qū)域，主要有以下幾種方法。

1-1使用Range屬性

VBA中可以使用Range屬性返回單元格或單元格區(qū)域，如下面的代碼所示。

#001SubRngSelect（）

#002Sheetl.Range（"A3:F6,Bl:C5"）.Select

#003EndSub

代碼解析：

RngSelect過程使用Select方法選中A3:F6,B1:C5單元格區(qū)域。

Range屬性返回一個Range對象，該對象代表一個單元格或單元格區(qū)域，語法如下：

Range（Celli,Cell2）

參數(shù)Celli是必需的，必須為A1樣式引用的宏語言，可包括區(qū)域操作符（冒號）、相

交區(qū)域操作符（空格）或合并區(qū)域操作符（逗號）。也可包括美元符號（即絕對地址，如

“$A$1嘰可在區(qū)域中任一部分使用局部定義名稱，如Range（"B2:LastCell"）,其中LastCell

為已定義的單元格區(qū)域名稱。

參數(shù)Cell2是可選的，區(qū)域左上角和右下角的單元格。

運(yùn)行SubRngSelect過程，選中A3:F6,B1:C5單元格區(qū)域，如圖1-1所示。

4

注意如果沒有使用對象識別符，Range屬性返回活動表的一個區(qū)域，如果活動表不是

工作表，則該屬性無效。

1-2使用Cells屬性

使用Cells屬性返回一個Range對象，如下面的代碼所示。

#001SubCell()

#002DimicellAsInteger

#003Foricell=1To100

#004Sheet2.Cells(icell,1).Value=icell

#005Next

#006EndSub

代碼解析：

Cell過程使用For...Next語句為工作表中的A1:A100單元格區(qū)域填入序號。

Cells屬性指定單元格區(qū)域中的單元格，語法如下：

Cells(Rowindex,ColumnIndex)

參數(shù)Rowindex是可選的，表示引用區(qū)域中的行序號。

參數(shù)Columnindex是可選的，表示引用區(qū)域中的列序號。

如果缺省參數(shù)，Cells屬性返回引用對象的所有單元格。

Cells屬性的參數(shù)可以使用變量，因此經(jīng)常應(yīng)用于在單元格區(qū)域中循環(huán)。

1-3使用快捷記號

在VBA中可以將A1引用樣式或命名區(qū)域名稱使用方括號括起來，作為Range屬性的

快捷方式，這樣就不必鍵入單詞“Range”或使用引號，如下面的代碼所示。

5

#001SubFastmark()

#002[Al：A5]=2

#003[Fast]=4

#004EndSub

代碼解析：

Fastmark過程使用快捷記號為單元格區(qū)域賦值。

第2行代碼使用快捷記號將活動工作表中的A1：A5單元格賦值為2o

第3行代碼將工作簿中已命名為“Fast”的單元格區(qū)域賦值為4。

注意使用快捷記號引用單元格區(qū)域時只能使用固定字符串而不能使用變量。

1-4使用Offset屬性

可以使用Range對象的Offset屬性返回一個基于引用的Range對象的單元格區(qū)域,如

下面的代碼所示。

#001SubOffset()

#002Sheet3.Range("Al:C3").Offset(3,3).Select

#003EndSub

代碼解析：

Offset過程使用Range對象的Offset屬性選中A1：A3單元格偏移三行三列后的區(qū)域。

應(yīng)用于Range對象的Offset屬性的語法如下：

expression.Offset(RowOffset,Columnoffset)

參數(shù)expression是必需的，該表達(dá)式返回一個Range對象。

參數(shù)RowOffset是可選的，區(qū)域偏移的行數(shù)(正值、負(fù)值或0(零))。正值表示向下

偏移，負(fù)值表示向上偏移，默認(rèn)值為0?

參數(shù)Columnoffset是可選的，區(qū)域偏移的列數(shù)(正值、負(fù)值或0(零))。正值表示向

右偏移，負(fù)值表示向左偏移，默認(rèn)值為0o

運(yùn)行Offset過程，選中A1：A3單元格偏稱三行三列后的區(qū)域，如圖1-2所示。

6

1-5使用Resize屬性

使用Range對象的Resize屬性調(diào)整指定區(qū)域的大小,并返回調(diào)整大小后的單元格區(qū)域,

如下面的代碼所示。

#001SubResize()

#002Sheet4.Range("Al").Resize(3,3).Select

#003EndSub

代碼解析：

Resize過程使用Range對象的Resize屬性選中A1單元格擴(kuò)展為三行三列后的區(qū)域。

Resize屬性的語法如下：

expression.Resize(RowSize,ColumnSize)

參數(shù)expression是必需的，返回要調(diào)整大小的Range對象

參數(shù)RowSize是可選的，新區(qū)域中的行數(shù)。如果省略該參數(shù)，則該區(qū)域中的行數(shù)保持

不變。

參數(shù)ColumnSize是可選的，新區(qū)域中的列數(shù)。如果省略該參數(shù)。則該區(qū)域中的列數(shù)保

持不變。

運(yùn)行Resize過程，選中A1單元格擴(kuò)展為三行三列后的區(qū)域，如圖1-3所示。

7

1-6使用Union方法

使用Union方法可以將多個非連續(xù)區(qū)域連接起來成為一個區(qū)域，從而可以實現(xiàn)對多個

非連續(xù)區(qū)域一起進(jìn)行操作，如下面的代碼所示。

#001SubUnSelect()

#002Union(Sheet5.Range(nAl:D4n),Sheet5.Range("E5:H8n)).Select

#003EndSub

代碼解析：

UnSelect過程選擇單元格A1:D4和E5:H8所組成的區(qū)域。Union方法返回兩個或多個

區(qū)域的合并區(qū)域，語法如下：

expression.Union(Argl,Arg2,...)

其中參數(shù)expression是可選的，返回一個Application對象。

參數(shù)Arg1,Arg2,…是必需的，至少指定兩個Range對象。

運(yùn)行UnSelect過程，選中單元格A1:D4和E5:H8所組成的區(qū)域，如圖1-4所示。

圖1-4使用Union方法將多個非連續(xù)區(qū)域連接成一個區(qū)域

1-7使用UsedRange屬性

使用UsedRange屬性返回指定工作表上已使用單元格組成的區(qū)域，如下面的代碼所示。

#001SubUseSelect()

#002Sheet6.UsedRange.Select

#003EndSu

代碼解析：

UseSelect過程使用UsedRange屬性選擇工作表上已使用單元格組成的區(qū)域，包括空

單元格。如工作表中已使用A1單元格和D8單元格，運(yùn)行UseSelect過程將選擇A1到D8

單元格區(qū)域，如圖1-5所示。

8

圖1-5使用UsedRange屬性選擇已使用區(qū)域

1-8使用CurrentRegion屬性

使用CurrentRegion屬性返回指定工作表上當(dāng)前的區(qū)域，如下面的代碼所示。

#001SubCurrentSelect()

#002Sheet7.Range("A5").CurrentRegion.Select

#003EndSub

代碼解析：

CurrentSelect過程使用CurrentRegion屬性選擇工作表上A5單元格當(dāng)前的區(qū)域，當(dāng)

前區(qū)域是一個邊緣是任意空行和空列組合成的范圍。

運(yùn)行CurrentSelect過程將選擇A5到B6單元格區(qū)域，如圖1-6所示。

圖1-6CurrentRegion屬性選擇當(dāng)前的區(qū)域

技巧2選定單元格區(qū)域的方法

2-1使用Select方法

在VBA中一般使用Select方法選定單元格或單元格區(qū)域，如下面的代碼所示。

9

#001SubRngSelect()

#002Sheet3.Activate

#003Sheet3.Range("Al：BIO").Select

#004EndSub

代碼解析：

RngSelect過程使用Select方法選定Sheets中的A1：B10單元格區(qū)域，Select方法

應(yīng)用于Range對象時語法如下：

expression.Select(Replace)

參數(shù)expression是必需的，一個有效的對象。

參數(shù)Replace是可選的，要替換的對象。

使用Select方法選定單元格時，單元格所在的工作表必需為活動工作表，所以在第2

行代碼中先使用Activate方法使Sheet3成為活動工作表，否則Select方法有可能出錯，顯

示如圖2-1所示的錯誤提示。

圖2-1Select方法無效提示

2-2使用Activate方法

還可以使用Activate方法選定單元格或單元格區(qū)域，如下面的代碼所示。

#001SubRngActivate()

#002Sheet3.Activate

#003Sheet3.Range("Al:B10").Activate

#004EndSub

代碼解析：

RngActivate過程使用Activate方法選定Sheet3中的A1：B10單元格區(qū)域，Activate

方法應(yīng)用于Range對象時語法如下：

expression.Activate

使用Activate方法選定單元格時，單元格所在的工作表也必需為活動工作表，否則

Activate方法有可能出錯，顯示如圖2-2所示的錯誤提示。

10

licrosoftVisualBasic

運(yùn)行時錯誤'1004’:

類Range的Activate方法無效

鏤的|結(jié)束②|I調(diào)匐EHI幫助00|

圖2-2Activate方法無效提示

2-3使用Goto方法

使用Got。方法無需使單元格所在的工作表成為活動工作表，如下面的代碼所示。

#001SubRngGoto()

#002Application.GotoReference:=Sheet3.Range("Al：B10"),scroll:=True

#003EndSub

代碼解析：

RngGoto過程使用Goto方法選定Sheets中的A1：B10單元格區(qū)域，并滾動工作表

以顯示該單元格。

Goto方法選定任意工作簿中的任意區(qū)域或任意VisualBasic過程，并且如果該工作簿

未處于活動狀態(tài)，就激活該工作簿，語法如下：

expression.Goto(Reference,Scroll)

參數(shù)expression是必需的，返回一個Application對象。

參數(shù)Reference是可選的，Variant類型，指定目標(biāo)?？梢允荝ange對象、包含R1C1-

樣式記號的單元格引用的字符串或包含VisualBasic過程名的字符串。如果省略本參數(shù)，

目標(biāo)將是最近一次用Goto方法選定的區(qū)域。

參數(shù)Scroll是可選的，Variant類型，如果該值為True,則滾動窗口直至目標(biāo)區(qū)域的左

上角單元格出現(xiàn)在窗口的左上角。如果該值為False,則不滾動窗口。默認(rèn)值為False。

技巧3獲得指定行、列中的最后一個非空單元格

使用VBA對工作表進(jìn)行操作時，經(jīng)常需要定位到指定行或列中最后一個非空單元格，

此時可以使用Range對象的End屬性，在取得單元格對象后便能獲得該單元格的相關(guān)屬性,

11

如單元格地址、行列號、數(shù)值等，如下面的代碼所示。

#001SubLastRow()

#002DimrngAsRange

#003Setrng=Sheet1.Range("A65536").End(xlUp)

#004MsgBox"A列中最后一個非空單元格是"&rng.Address(0,0)_

#005&",行號"&rng.Row&"，數(shù)值"&rng.Value

#006Setrng=Nothing

#007EndSub

代碼解析：

LastRow過程使用消息框顯示工作表中A列最后非空單元格的地址、行號和數(shù)值。

End屬性返回一個Range對象，該對象代表包含源區(qū)域的區(qū)域尾端的單元格。等同于

按鍵＜End+向上鍵〉、＜End+向下鍵〉、＜End+向左鍵＞或＜￡血+向右鍵〉，語法如下：

expression.End(Direction)

參數(shù)expression是必需的，一個有效的對象。

參數(shù)Direction是可選的，所要移動的方向，可以為表格3-1所示的XIDirection常量

之一。

常量值描述

xlDown-4121向下

xlToRight-4161向右

xlToLeft-4159向左

xlUp-4162向上

表格3-1XIDirection常量

Range對象的End屬性返回的是一個Range對象，因此可以直接使用該對象的屬性和

方法。

運(yùn)行LastRow過程結(jié)果如圖3-1所示。

12

通過修改相應(yīng)的參數(shù)，能夠獲得指定行中最后一個非空單元格，如下面的代碼所示。

#001SubLastColumn()

#002DimrngAsRange

#003Setrng=Sheetl.Range(nIVln).End(xlToLeft)

#004MsgBox”第一行中最后一個非空單元格是,，&rng.Address(0,0)_

#005&"，歹ll號"&rng.Column&"，數(shù)值"&rng.Value

#006Setrng=Nothing

#007EndSub

代碼解析：

LastColumn過程使用消息框顯示工作表中第一行最后一個非空單元格的地址、列號和

數(shù)值，如圖3-2所示。

13

技巧4定位單元格

在Excel中使用定位對話框可以選中工作表中特定的單元格區(qū)域，而在VBA中則使用

SpecialCells方法，如下面的代碼所示。

#001SubSpecialAddress()

#002DimrngAsRange

#003Setrng=Sheet1.UsedRange.SpecialCells(xlCellTypeFormulas)

#004rng.Select

#005MsgBox”工作表中有公式的單元格為：”&rng.Address

#006Setrng=Nothing

#007EndSub

代碼解析：

SpecialAddress過程使用SpecialCells方法選中工作表中有公式的單元格，并用消息

框顯示其地址。

SpecialCells方法返回一個Range對象，該對象代表與指定類型及值相匹配的所有單

元格，語法如下：

expression.SpecialCells(Type,Value)

參數(shù)expression是必需的，返回一個有效的對象。

參數(shù)Type是必需的，要包含的單元格，可為表格4-1所列的XICellType常量之一。

常量值描述

xlCellTypeAIIFormatConditions-4172任意格式單元格

xlCellTypeAIIValidation-4174含有驗證條件的單元格

xlCellTypeBlanks4空單元格

xlCellTypeComments-4144含有注釋的單元格

xlCellTypeConstants2含有常量的單元格

xlCellTypeFormulas-4123含有公式的單元格

xlCellTypeLastCell11使用區(qū)域中最后的單元格

xlCellTypeSameFormatConditions-4173含有相同格式的單元格

xlCellTypeSameValidation-4175含有相同驗證條件的單元格

xlCellTypeVisible12所有可見單元格

表格4-1XICellType常量

第3行代碼將SpecialCells方法的Type參數(shù)設(shè)置為xlCellTypeFormulas,返回的是含

有公式的單元格，通過修改相應(yīng)的參數(shù)可以返回不同的單元格。

14

參數(shù)Value是可選的，如果Type參數(shù)為xlCellTypeConstants或xlCellTypeFormulas,

此參數(shù)可用于確定結(jié)果中應(yīng)包含哪幾類單元格。將某幾個值相加可使此方法返回多種類型的

單元格。如果省略將選定所有常量或公式，可為表格4-2所列的XISpecialCellsValue常量

之一。

常量值描述

xIErrors16錯誤

xILogical4邏輯值

xINumbers1數(shù)字

xlTextValues2文本

表格4-2XISpecialCellsValue常量

第5行代碼使用消息框顯示工作表中含有公式單元格的地址。SpecialCells方法返回的

是Range對象，因此可以直接使用該對象的屬性和方法。

運(yùn)行SpecialAddress過程結(jié)果如圖4-1所示。

技巧5查找單元格

5-1使用Find方法

在Excel中使用查找對話框可以查找工作表中特定內(nèi)容的單元格，而在VBA中則使用

Find方法，如下面的代碼所示。

#001SubRngFindO

#002DimStrFindAsString

15

#003DimRngAsRange

#004StrFind=工nputBox(“請輸入要查找的值：“)

#005IfTrim(StrFind)<>"”Then

#006WithSheetl.Range("A:A")

#007SetRng=.Find(What:=StrFind,_

#008After:=.Cells(.Cells.Count),_

#009Lookin:=xlValues,_

#010LookAt:=xlWhole,_

#011SearchOrder:=xlByRows,_

#012SearchDirection:=xlNextA_

#013MatchCase:=False)

#014IfNotRngIsNothingThen

#015Application.GotoRng,True

#016Else

#017MsgBox”沒有找到該單元格！”

#018EndIf

#019EndWith

#020EndIf

#021EndSub

代碼解析:

RngFind過程使用Find方法在工作表Sheetl的A列中查找InputBox函數(shù)對話框中所

輸入的值，并查找該值所在的第一個單元格。

第6到第13行代碼在工作表Sheetl的A列中查找InputBox函數(shù)對話框中所輸入的值。

應(yīng)用于Range對象的Find方法在區(qū)域中查找特定信息，并返回Range對象，該對象代表

用于查找信息的第一個單元格。如果未發(fā)現(xiàn)匹配單元格，就返回Nothing,語法如下：

expression.Find(What,After,Lookin,LookAt,SearchOrder,SearchDirection,

MatchCase,MatchByte,SerchFormat)

參數(shù)expression是必需的，該表達(dá)式返回一個Range對象。

參數(shù)What是必需的，要搜索的數(shù)據(jù)，可為字符串或任意數(shù)據(jù)類型。

參數(shù)After是可選的，表示搜索過程將從其之后開始進(jìn)行的單元格，必須是區(qū)域中的單

個單元格。查找時是從該單元格之后開始的，直到本方法繞回到指定的單元格時，才對其進(jìn)

行搜索。如果未指定本參數(shù)，搜索將從區(qū)域的左上角單元格之后開始。

在本例中將After參數(shù)設(shè)置為A列的最后一個單元格，所以查找時從A1單元格開始搜

16

索。

參數(shù)Lookln是可選的，信息類型。

參數(shù)LookAt是可選的，可為XILookAt常量的xlWhole或xIPart之一。

參數(shù)SearchOrder是可選的，可為XISearchOrder常量的xlByRows或xlByColumns

之一。

參數(shù)SearchDirection是可選的，搜索的方向，可為XISearchDirection常量的xINext

或xIPrevious之一。

參數(shù)MatchCase是可選的，若為True,則進(jìn)行區(qū)分大小寫的查找。默認(rèn)值為False。

參數(shù)MatchByte是可選的，僅在選擇或安裝了雙字節(jié)語言支持時使用。若為True,則

雙字節(jié)字符僅匹配雙字節(jié)字符。若為False,則雙字節(jié)字符可匹配其等價的單字節(jié)字符。

參數(shù)SerchFormat是可選的，搜索的格式。

每次使用Find方法后，參數(shù)Lookln、LookAt>SearchOrder和MatchByte的設(shè)置將

保存。如果下次調(diào)用Find方法時不指定這些參數(shù)的值，就使用保存的值。因此每次使用該

方法時請明確設(shè)置這些參數(shù)。

如果工作表的A列中存在重復(fù)的數(shù)值，那么需要使用FindNext方法或FindPrevious

方法進(jìn)行重復(fù)搜索，如下面的代碼所示。

#001SubRngFindNext()

#002DimStrFindAsString

#003DimRngAsRange

#004DimFindAddressAsString

#005StrFind=工nputBox（"請輸入要查找的值：”）

#006IfTrim(StrFind)<>""Then

#007WithSheetl.Range("A:A")

#008SetRng=.Find(What:=StrFind,_

#009After:=.Cells(.Cells.Count),_

#010Lookln:=xlValues,_

#011LookAt:=xlWhole,_

#012SearchOrder:=xlByRows,_

#013SearchDirection:=xlNextf_

#014MatchCase:=False)

#015IfNotRngIsNothingThen

#016FindAddress=Rng.Address

#017Do

17

#018Rng.Interior.ColorIndex=6

#019SetRng=.FindNext(Rng)

#020LoopWhileNotRngIsNothingAndRng.Address<>

FindAddress

#021EndIf

#022EndWith

#023EndIf

#024EndSub

代碼解析：

RngFindNext過程在工作表Sheetl的A列中查找InputBox函數(shù)對話框中所輸入的值,

并將查到單元格底色設(shè)置成黃色。

第8行到第17行代碼使用Find方法在工作表Sheetl的A列中查找。

第16行代碼將查找到的第一個單元格地址賦給字符串變量FindAddresSo

第18行代碼將查找到的單元格底色設(shè)置成黃色。

第19行代碼使用FindNext方法進(jìn)行重復(fù)搜索。FindNext方法繼續(xù)執(zhí)行用Find方法啟

動的搜索。查找下一個匹配相同條件的單元格并返回代表單元格的Range對象，語法如下:

expression.FindNext(After)

參數(shù)expression是必需的，返回一個Range對象。

參數(shù)After是可選的，指定一個單元格，查找將從該單元格之后開始。

第20行代碼如果查找到的單元格地址等于字符串變量FindAddress所記錄的地址，說

明A列已搜索完畢，結(jié)束查找過程。

運(yùn)行RngFindNext過程，在InputBox函數(shù)輸入框中輸入“196.01”后結(jié)果如圖5-1

所示。

-3I676.56.

-

4810.59

5917.48

6102.06

7906.84

8196.01

9109.39

631.28

10

11827.74

12925.82

1346.79

14

15737.32

16416.29

17

557.14

439.34

196.01

圖5-1使用FindNext方法重復(fù)搜索

還可以使用FindPrevious方法進(jìn)行重復(fù)搜索，F(xiàn)indPrevious方法的語法如下：

expression.FindPrevious(After)

18

FindPrevious方法和FindNext方法唯一的區(qū)別是FindPrevious方法查找匹配相同條件

的前一個單元格而FindNext方法是查找匹配相同條件的下一個單元格。

5-2使用Like運(yùn)算符

使用Like運(yùn)算符可以進(jìn)行更為復(fù)雜的模式匹配查找，如下面的代碼所示。

#001SubRngLike()

#002DimrngAsRange

#003DimaAsInteger

#004a=1

#005WithSheet2

#006.Range("A:A").ClearContents

#007ForEachrngIn.Range("Bl:E1000")

#008Ifrng.TextLike"*a***Then

#009.Range("A"&a)=rng.Text

#010a=a+1

#011EndIf

#012Next

#013EndWith

#014EndSub

代碼解析：

RngLike過程使用ForEach...Next語句和Like運(yùn)算符在單元格區(qū)域B1:E10000中搜

索含有“a”字符的單元格，找到匹配單元格以后將單元格的值寫入到A列中。

第6行代碼使用ClearContents方法清除A列區(qū)域的數(shù)據(jù)。

第7行代碼使用ForEach...Next語句在單元格區(qū)域B1:E10000中循環(huán)。

第8行代碼使用Like運(yùn)算符在單元格區(qū)域B1:E10000中搜索含有“a”字符的單元格。

Like運(yùn)算符用來比較兩個字符串，語法如下：

result=stringLikepattern

參數(shù)string是必需的，字符串表達(dá)式。

參數(shù)pattern是必需的，字符串表達(dá)式。

如果string與pattern匹配，貝ijresult為True；如果不匹配，貝ijresult為Falseo但是

如果string或pattern中有一個為Null,則result為Null。

參數(shù)pattern可以使用通配符、字符串列表或字符區(qū)間的任何組合來匹配字符串。表格

19

5-1列出pattern中允許的字符以及它們與什么進(jìn)行匹配。

pattern中的字符符合string中的字符

?任何單一字符

*零個或多個字符

#任何一個數(shù)字(0-9)

[charlist]charlist中的任何單一字符

[!charlist]不在charlist中的任何單一字符

表格5-1pattern中的匹配字符串

第9行代碼將找到的匹配單元格的值寫入到A列中。

運(yùn)行RngLike過程結(jié)果如圖5-2所示。

1

一

213

一4

一5

一6

一7

一8

一9

一

1PJ

nJ

衛(wèi)

13J

141

151

”

圖5-2使用Like運(yùn)算符進(jìn)行模式匹配查找

技巧6替換單元格內(nèi)字符串

如果需要替換單元格內(nèi)指定的字符串，那么使用Range對象的Replace方法，如下面

的代碼所示。

#001SubRngReplace()

#002Range(nAl:A5").Replace“通州“，“南通“

#003EndSub

代碼解析：

RngReplace過程將工作表A1:A5單元格中的“通州”字符串替換成“南通”字符串。

20

應(yīng)用于Range對象的Replace方法替換指定區(qū)域內(nèi)單元格中的字符，語法如下：

expression.Replace(What,Replacement,LookAtfSearchOrderAMatchCase,

MatchByte,SearchFormat,ReplaceFormat)

其中參數(shù)expression是必需的，返回一個Range對象。

參數(shù)What是必需的，要搜索的字符串。

參數(shù)Replacement是必需的，替換的字符串。

運(yùn)行RngReplace過程前工作表如圖6-1所示，運(yùn)行RngReplace過程后結(jié)果如圖6-2

所示。

1通州市建設(shè)局

2通州市房產(chǎn)局

3通州i市水利局

4通州市自來水公司

5通州市安裝維修公司

6

圖6-1替換前單元格

1南通市建設(shè)局

2南通市房產(chǎn)局

3南通市水利局

4南通市自來水公司

5南通市安裝維修公司

6

圖6-2替換后單元格

技巧7復(fù)制單元格區(qū)域

在實際操作中，經(jīng)常需要復(fù)制指定的單元格區(qū)域到另外一個單元格區(qū)域。要復(fù)制指定單

元格區(qū)域到其他位置，使用Range對象的Copy方法，如下面的代碼所示。

#001SubRangeCopy()

#002Application.DisplayAlerts=False

#003Sheet1.Range("Al").CurrentRegion.CopySheet2.Range("Al")

21

#004Application.DisplayAlerts=True

#005EndSub

代碼解析:

RangeCopy過程將如圖7-1所示的Sheetl工作表中A1單元格的當(dāng)前區(qū)域復(fù)制到

Sheet2工作表中以A1單元格為左上角單元格的區(qū)域，如圖7-2所示。

cABCDEFG

后編號

1技能工資崗位工資工齡工資浮動工資