目录
第1章 Range（单元格）对象（范例1~范例17） 第2章 Sheet（工作表）对象（范例18~范例37） 第6章 控件与用户窗体（范例67 ~ 范例112） 第7章 使用对话框（范例113 ~ 范例118） 第9章 文件操作（范例134 ~ 范例143）
范例1 单元格的引用方法(返回)
使用Range属性引用单元格区域
Sub MyRng()
Range(\Range(\Range(\
Range(\End Sub
使用Cells属性引用单元格区域
Sub MyCell()
Dim i As Byte For i = 1 To 10
Sheets(\Next End Sub
使用快捷记号实现快速输入
Sub FastMark()
[A1] = \End Sub
使用Offset属性返回单元格区域
Sub RngOffset()
Sheets(\End Sub
1
使用Resize属性返回调整后的单元格区域
Sub RngResize()
Sheets(\End Sub
范例2 选定单元格区域的方法
使用Select方法
Sub RngSelect()
Sheets(\
Sheets(\End Sub
使用Activate方法
Sub RngActivate()
Sheets(\
Sheets(\End Sub
使用Goto方法
Sub RngGoto()
Application.Goto Reference:=Sheets(\：B10\End Sub
范例3 获得指定行的最后一个非空单元格
Sub LastCell()
Dim rng As Range
Set rng = Cells(Rows.Count, 1).End(xlUp)
MsgBox \列的最后一个非空单元格是\
& \行号\数值\Set rng = Nothing End Sub
范例4 使用SpecialCells方法定位单元格
Sub SpecialAddress()
Dim rng As Range
Set rng = Sheet1.UsedRange.SpecialCells(xlCellTypeFormulas)
2
rng.Select
MsgBox \工作表中有公式的单元格为： \Set rng = Nothing End Sub
范例5 查找特定内容的单元格
使用Find方法查找特定信息
Sub FindCell()
Dim StrFind As String Dim rng As Range
StrFind = InputBox(\请输入要查找的值：\If Len(Trim(StrFind)) > 0 Then
With Sheet1.Range(\
Set rng = .Find(What:=StrFind, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _
SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False)
If Not rng Is Nothing Then Application.Goto rng, True Else
MsgBox \没有找到匹配单元格!\End If End With End If
Set rng = Nothing End Sub
Sub FindNextCell()
Dim StrFind As String Dim rng As Range
Dim FindAddress As String
StrFind = InputBox(\请输入要查找的值：\If Len(Trim(StrFind)) > 0 Then
With Sheet1.Range(\
.Interior.ColorIndex = 0
Set rng = .Find(What:=StrFind, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _
SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False)
If Not rng Is Nothing Then FindAddress = rng.Address
3
Do
rng.Interior.ColorIndex = 6 Set rng = .FindNext(rng) Loop While Not rng Is Nothing _
And rng.Address <> FindAddress End If End With End If
Set rng = Nothing End Sub
使用Like运算符进行模式匹配查找
Sub RngLike()
Dim rng As Range Dim r As Integer r = 1
Sheet1.Range(\
For Each rng In Sheet2.Range(\
If rng.Text Like \
Cells(r, 1) = rng.Text r = r + 1 End If Next
Set rng = Nothing End Sub
范例6 替换单元格内字符串
Sub Replacement()
Range(\
What:=\市\区\
LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=True
End Sub
范例7 单元格的复制
复制单元格区域
Sub RangeCopy()
Sheet1.Range(\End Sub
Sub Copyalltheforms()
Dim i As Integer
Sheet1.Range(\4
With Sheet3.Range(\
.PasteSpecial xlPasteAll
.PasteSpecial xlPasteColumnWidths End With
Application.CutCopyMode = False For i = 1 To 7
Sheet3.Rows(i).RowHeight = Sheet1.Rows(i).RowHeight Next End Sub
仅复制数值到另一区域
Sub CopyValue()
Sheet1.Range(\
Sheet2.Range(\Application.CutCopyMode = False End Sub
Sub GetValueResize()
With Sheet1.Range(\
Sheet3.Range(\End With End Sub
范例8 禁用单元格拖放功能
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Application.CellDragAndDrop = False Else
Application.CellDragAndDrop = True End If End Sub
Private Sub Worksheet_Deactivate()
Application.CellDragAndDrop = True End Sub
范例9 设置单元格格式
设置单元格字体格式
Sub CellFont()
With Range(\
.Name = \华文彩云\.FontStyle = \
5