全民一起VBA实战篇第二课：表格数据处理的实用技巧

最新推荐文章于 2022-10-21 18:39:45 发布

高开低走。

最新推荐文章于 2022-10-21 18:39:45 发布

阅读量745

点赞数

分类专栏：锦到说VBA

本文链接：https://blog.csdn.net/qq_43568982/article/details/104097807

版权

锦到说VBA 专栏收录该内容

37 篇文章 21 订阅

订阅专栏

Find方法

官方文档
https://docs.microsoft.com/zh-TW/office/vba/api/excel.range.find

内容可使用通配符

符号	含义
?	一个任意字符，比如?a?
*	任意多个任意字符，a
~	转义符，把通配符改为普通字符串

题目一

题目要求：

（1）找到第一个内容为“程序员”的单元格，并用Msgbox显示其单元格地址
（2）找到第一个包含“员”字的单元格，并且用Msgbox显示其单元格地址（提示：可以使用星号通配符）
（3）找到第一个形如“X员X”字的单元格，并且用Msgbox显示其单元格地址。（提示：可以使用问号通配符）

题目文件：https://study.163.com/course/courseLearn.htm?courseId=1004331019&share=1&shareId=1020478736#/learn/video?lessonId=1048569014&courseId=1004331019

方法一：Range遍历循环

Sub FindCoder()
    Dim j As Long, arr()   
    Dim t   
    t = Timer()    
    arr = Range(Cells(1, 1), Cells(50000, 10))
    '用数组存放range   
    For i = 1 To 50000    
        For j = 1 To 10            
            If arr(i, j) = "程序员" Then            
                MsgBox Cells(i, j).Address           
            	GoTo found           
            End If
        Next j
    Next i    
	found:
    MsgBox "一共用时" & Timer() - t & "秒"    
End Sub

方法二：用FIND方法

Sub FindCoder2()
    Dim j As Long, arr() 
    Dim t
    Dim r As Range
    t = Timer()    
    Set r = Range(Cells(1, 1), Cells(50000, 10)).Find("?员?")
    '?通配符
    If Not r Is Nothing Then
    '避免出现没有找到的情况而报错
        r.Select
        MsgBox r.Address       
    Else
        MsgBox "没有该字符"       
    End If    
    MsgBox "一共用时" & Timer() - t & "秒"
End Sub

两种方法的运行效率都非常高，但是代码来说，用FIND方法更为简洁

题目二

该作业要求大家使用Find方法，找出本表格A列每个人名在“第1次会议”到“第20次会议”共20张表格中的出席情况。比如第4行的“陈彬”出席过第13次和17次会议，则在N4和R4两个单元格中都写入“Yes”，而第4行其他单元格留白。提示：本表格第一行每个单元格的内容，正好是每次会议工作表的表名（Worksheet.Name) 属性，可以直接利用这一点实现循环读取全部会议表格。

在这里插入图片描述

自己写的代码

Sub demo()
    Dim t
    Dim i As Long, j As Long
    Dim ws As Worksheet
    Dim w As Worksheet
    Dim r As Range
    Set ws = Worksheets("出席统计")
    t = Timer()
    i = 2
    Do While ws.Cells(i, 1) <> ""
         For j = 2 To 21
            Set w = Worksheets("第" & j - 1 & "次会议")
            '指定对应文件簿
            Set r = w.UsedRange.Find(ws.Cells(i, 1))
            '运用Find属性，这里用的时候不能分开
            '先定义r再r.Find好像不太行
            If Not r Is Nothing Then
                ws.Cells(i, j) = "Yes"
            Else
                ws.Cells(i, j) = "No"
            End If
        Next j
    i = i + 1
    Loop               
    MsgBox "一共用时为" & Timer() - t & "秒"    
End Sub

运行两分半

参考答案代码

Sub demo()
    Dim t
    t = Timer
    Dim i As Long, j As Long, name As String, meetName As String
    Dim wAll As Worksheet, wMeet As Worksheet, r As Range
    Set wAll = Worksheets("出席统计")    
    i = 2
    Do While Trim(wAll.Cells(i, 1)) <> ""        
        name = Trim(wAll.Cells(i, 1))        
        For j = 2 To 21        
            meetName = wAll.Cells(1, j).Value
            '一定要用.Value，不然就要报错
            '我没有写这个就是因为忘了这点            
            Set wMeet = Worksheets(meetName)            
            Set r = wMeet.Cells.Find(name)            
            If Not r Is Nothing Then
            	wAll.Cells(i, j) = "Yes"
            Else
            	wAll.Cells(i, j) = "No"
            End If
        Next j       
        i = i + 1    
    Loop
    MsgBox "一共用时" & Timer() - t & "秒"
End Sub

一共运行2分钟，效率还是接近的

题目三

https://study.163.com/course/courseLearn.htm?courseId=1004331019&share=1&shareId=1020478736#/learn/video?lessonId=1048572029&courseId=1004331019

（1）找到第一个内容含有“vba”的单元格，并用Msgbox显示其单元格地址
（2）找到第一个内容精确等于“vba”的单元格，并且用Msgbox显示其单元格地址
（3）找到第一个背景色为红色，且包含有大写字母“VBA”的单元格，并且用Msgbox显示其单元格地址。
（4）找到第一个内容为“GWb”的单元格，并且大小写精确匹配。
（5）如果在任务（4）中找到的是E5单元格，请修改LookIn参数，使之返回C15单元格；若任务（4）找到的是C15，则修改LookIn使之返回E5。

问题（1）

Sub findDemo()
    Dim r As Range
    Set r = Cells.Find("vba", MatchCase:=False)
    'matchcase匹配大小写，true为敏感，注意:=这个写法
    If Not r Is Nothing Then
        MsgBox r.Address
    End If
End Sub

问题（2）

Sub findDemo2()
    Dim r As Range
    Set r = Cells.Find("vba", lookat:=xlWhole)
    'lookat精准匹配，xlwhole为完全，xlpart为部分
    If Not r Is Nothing Then
        MsgBox r.Address
    End If
End Sub

问题（3）

Sub findDemo3()
    Dim r As Range
    Application.FindFormat.Interior.Color = vbRed
    Set r = Cells.Find("VBA", MatchCase:=True, lookat:=xlPart, searchformat:=True)
    '匹配大小写，匹配部分，匹配格式
    If Not r Is Nothing Then
        MsgBox r.Address
        r.Select
    End If
End Sub

问题（4）

Sub findDemo4()
    Dim r As Range
    Set r = Cells.Find("GWb", MatchCase:=True, lookat:=xlPart, LookIn:=xlValues)
    '按照值来查找结果，xlFormulas，按照公式来给结果
    If Not r Is Nothing Then
        MsgBox r.Address
        r.Select
    End If
End Sub

题目四

https://study.163.com/course/courseLearn.htm?courseId=1004331019&share=1&shareId=1020478736#/learn/video?lessonId=1048568029&courseId=1004331019

（1）找到表格中左上角开始第一个内容为“程序员”的单元格，并用Msgbox显示其单元格地址（答案：I13）
（2）找到表格中最后一个内容为“程序员”的单元格，并且用Msgbox显示其单元格地址（答案：I44289）
（3）找到第100行后面的第一个内容为“程序员”的单元格，并且用Msgbox显示其单元格地址（答案：D171）
（4）找到表格里所有内容为“程序员”的单元格中，处于最右边（即列号最大）的单元格，并用Msgbox显示其地址（答案：J82）

问题（1）

Sub FindDemo1()
	Dim r As Range
	Dim w As Worksheet
	Set w = Worksheets("Sheet1")
	Set r = w.UsedRange.Find("程序员")	
	If Not r Is Nothing Then
	    MsgBox r.Address
	End If
End Sub

问题（2）

Sub FindDemo2()
	Dim r As Range
	Dim w As Worksheet
	Set w = Worksheets("Sheet1")
	Set r = w.UsedRange.Find("程序员", searchorder:=xlByRows, searchdirection:=xlPrevious)
	'找最后一个
	If Not r Is Nothing Then
	    MsgBox r.Address
	End If
End Sub

问题（3）

Sub FindDemo3()
	Dim r As Range
	Dim w As Worksheet
	Set w = Worksheets("Sheet1")
	Set r = w.UsedRange.Find("程序员", after:=Range("$B101"))
	'after后面是一个range对象，从这之后开始查找，不包括这一个
	If Not r Is Nothing Then
	    MsgBox r.Address
	End If
End Sub

问题（4）

Sub FindDemo4()
	Dim r As Range
	Dim w As Worksheet
	Set w = Worksheets("Sheet1")
	Set r = w.UsedRange.Find("程序员", searchorder:=xlByColumns, searchdirection:=xlPrevious)
	'按列查找,反向查找（从右往左，从下到上）
	If Not r Is Nothing Then
	    MsgBox r.Address
	End If
End Sub

注意：这些参数有记忆功能，当操作过一次，设置过一次后，即使不给这些参数，也默认为上次的设置，此外，哪怕是操作EXCEL里面的查找，改了选项，也会对这里造成影响
写的时候，最好写全

Set r = w.UsedRange.Find("程序员", LookIn:=xlFormulas, lookat:=xlWhole, matchbyte:=False, searchorder:=xlByRows, searchdirection:=xlPrevious)

题目五

在这里插入图片描述

任务一：编写程序，找出第3列最后一行的行号。
任务二：编写程序，找出B到G列数据构成的整个表格的最后一列行号（答案：23）。
任务三：编写程序，计算B到G列每一列最后一行数字的总和（答案：2498）。

任务一：

方法一

利用Range.End(direction)
不足之处在于，如果是一张空表，会返回第一行回来，只能处理可见单元格，隐藏和筛选不能找出来
只能处理单行或者单列

Sub Demo()
	Dim r As Range
	Set r = Cells(Rows.Count, 3).End(xlUp)
	'range.end(direction),相当于在键盘上用Ctrl+方向键	
	MsgBox r.Row
End Sub

方法二

Range.SpecialCells(type,value)
类似于Excel本身的定位条件，用来找格子

Sub Special()
	Dim r As Range
    Set r = Cells.SpecialCells(xlCellTypeLastCell)
    '修改过格式，或者刚刚被删除，那么这种情况更新不过来
    '还是会被认为是这个范围内
    '上面的语法其实和Usedrange是一样的
	MsgBox r.Row
End Sub

方法三

用Find来找

Sub Special()
	Dim r As Range
    Set r = Cells.Find("*", after:=Range("a1"), searchorder:=xlRows, searchdirection:=xlPrevious)
    '修改过格式，或者刚刚被删除，那么这种情况更新得过来
    '能处理隐藏，但不能处理筛选
    If r Is Nothing Then
        MsgBox "表格中没有数据"
    Else
        MsgBox r.Row
    End If
End Sub

任务二：

Sub Demo2()
	Dim i As Long
	Dim Max As Long
	Dim r As Range
	Max = 0
	For i = 2 To 7
	    Set r = Cells(Rows.Count, i).End(xlUp)
	    If r.Row > Max Then
	        Max = r.Row
	    End If
	Next i
	MsgBox Max
End Sub

任务三：

Sub Demo3()
	Dim i As Long
	Dim Count As Long
	Dim r As Range
	Count = 0
	For i = 2 To 7
	    Set r = Cells(Rows.Count, i).End(xlUp)
	    Count = Count + r.Value
	Next i
	MsgBox Count
End Sub

返璞归真

利用循环，倒序遍历

Sub 遍历()
	Dim i As Long
	i = Rows.Count
	Do While i > 0
	    If Cells(i, 2) <> "" Then Exit Do
	    i = i - 1
	Loop
	MsgBox "最后一行是" & i & "行"
End Sub

可以避免什么格式修改，删除，筛选之类的问题，但是效率很低很低

题目六

请大家使用FindNext方法和DO…Loop While循环结构，找出所有内容为“程序员”的单元格，并将其颜色标记为红色。运行后，可以在Excel菜单中直接运行“查找”方法，依次检查程序运行结果是否正确。接下来，请大家使用Replace方法，将所有“程序员”单元格，都替换为红色背景的“会计师”。

Sub 找到Next()
	Dim r As Range
	Dim t
	t = Timer()
	Set r = Application.ActiveSheet.UsedRange.Find("会计师", _
	LookIn:=xlValues, lookat:=xlWhole, matchbyte:=False, searchorder:=xlByRows, searchdirection:=xlPrevious)
     If Not r Is Nothing Then
        firstAddress = r.Address
        Do
            r.Value = "程序员"
            r.Interior.Color = vbYellow
            Set r = ActiveSheet.UsedRange.FindNext(r)
            '往下定位
        If r Is Nothing Then
            GoTo DoneFinding
            '跳出循环
        End If
        Loop While r.Address <> firstAddress
    End If
	DoneFinding:
	MsgBox "一共用时" & Timer() - t & "秒"
End Sub

小结

在这里插入图片描述

高开低走。

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
全民一起VBA实战篇第二课：表格数据处理的实用技巧

Find方法官方文档https://docs.microsoft.com/zh-TW/office/vba/api/excel.range.find内容可使用通配符符号含义?一个任意字符，比如?a?*任意多个任意字符，a~转义符，把通配符改为普通字符串题目一题目要求：（1）找到第一个内容为“程序员”的单元格，并用Msgbox显示其单元格地...
复制链接

扫一扫

专栏目录