【原】《神奇的VBA》编程：快速定位报表列

神奇的ExcelVBA 2021-07-28

展开全文

------ 本篇前言------

对于经常使用VBA编程来处理日常报表数据的职场朋友们，经常会需要根据报表的列名来定位绝对列号，便于后续对该列中的所有单元格进行遍历判断分析等操作。对于VBA初学者们，常见方式是使用肉眼来定位需求列的列号，例如，下面示例报表中我们定位列名为“设备名称”的列号。肉眼一看就是E列，即第5列。

------ 需求案例------

案例1：将报表“设备名称”列中，所有含有“车”字样的单元格标记为红色。

对于有VBA入门基础的朋友们，很快就能通过简单遍历循环写出如下最简单的代码解决方案，快速解决工作中的场景需求。

Sub demo1()

'遍历E列E2:E11单元格区域For x = 2 To 11 If Cells(x, "E") Like "*车*" Then Cells(x, "E").Interior.Color = vbYellow End IfNext

End Sub

上面代码中，最原始的使用了Cells(x, "E")来定位和引用E列中相关单元格对象。

也可使用列号数值5直接标识E列，将上面Cells(x, "E")改成Cells(x, 5)。

Sub demo2()

'遍历E列E2:E11单元格区域For x = 2 To 11 If Cells(x, 5) Like "*车*" Then Cells(x, 5).Interior.Color = vbYellow End IfNext

End Sub

上面的写法是很多职场人常用的编程方式，包括我本人在内也是这样，不花里胡哨，快速解决工作中的报表需求。对于VBA编程，只要能简单快速解决问题代码就是最好的代码。通过运行，非常顺利地得到了想要的结果。

但问题来了，不同公司的报表或者不同职场人的报表的形式不一定如上图这样规范。经常出现报表某名称列位置发生变化。这时候，通过上面代码就不能实现正确的结果。

怎么办呢？要经常改代码来解决吗？

本篇《神奇的VBA》编程将分享本人在多年职场办公中，定位列号常用的代码片段。有同样的需求的职场朋友们，可以收藏复用。记得点赞，转发和收藏本篇知识。请点击“在看”分享给更多的职场朋友们。

思路：创建自定义函数，通过遍历报表所有列名称，获取列号。

Function GetColumn(biao As Worksheet, fieldRowNumber As Byte, fieldname As String) As IntegerDim c As LongGetColumn = 0For c = 1 To biao.UsedRange.Columns.Count If Cells(fieldRowNumber, c) = fieldname Then GetColumn = c: Exit For End IfNextEnd Function

上面代码中，编写了一个名称为GetColumn()自定义函数，函数中有三个参数。

⬤ biao ：代表Worksheet类型对象

⬤ fieldRowNumber: 字节型参数，用于提示报表列所在行。

⬤ fieldname: 字符串型参数，用于查找的列名称描述。

例如：GetColumn(ActiveSheet, 1, "设备名称")，表示在活动报表中，从第一行报表表头所有列字段中查找名称为"设备名称"的列所在的列号,如果返回0则表示没有该"设备名称"列。

通过上面自定义函数，就能应付常见的报表的。无论报表中“设备名称”列如何变换位置，上面代码总能精准获取该列的列号。

根据上面分享的GetColumn()自定义函数，我们改写案例1中的代码如下。

Sub demo3()Dim n As Byten = GetColumn(ActiveSheet, 1, "设备名称") If n > 0 Then

For x = 2 To 11 If Cells(x, n) Like "*车*" Then Cells(x, n).Interior.Color = vbYellow End If Next Else MsgBox "报表中不存在" & Chr(34) & "设备名称" & Chr(34) & "列"End IfEnd Sub

通过运行也很好地实现了案例1的场景需求。

如果看到本篇文章朋友们对demo3代码和GetColumn()自定义函数不是很理解，说明VBA代码的基础还不牢靠，请下载安装《神奇的VBA》编程学习插件查阅和学习相关知识。注意上面代码中Chr(34)代表双引号。

上面GetColumn()自定义函数是按照从左到右的顺序来查找某名称列的列号的，那么如何从右到左呢？这就很简单了，For c = biao.UsedRange.Columns.Count To 1 Step -1反向倒叙遍历即可。

Function GetColumnbyReverse(biao As Worksheet, fieldRowNumber As Byte, fieldname As String) As IntegerDim c As LongGetColumn = 0For c = biao.UsedRange.Columns.Count To 1 Step -1 If Cells(fieldRowNumber, c) = fieldname Then GetColumn = c: Exit For End IfNextEnd Function

GetColumn()和GetColumnbyReverse()自定义函数只是查找报表中指定名称列第一次出现时的列号。假设报表中有多个名称为“设备名称”的列怎么办呢？

答案是：请优先规范您的 "报表" 格式

假设您的报表中真的需要有同名列，且我只想从左到右指定第2次出现的“设备名称”列，请进一步完善上面的自定义函数。

本篇分享的思路和代码抛砖引玉，思路有很多，熟悉VBA字典的应用的话，也可以使用字将报表列名称和列号作为键值对加入字典来快速定位报表列号。本篇分享就到这里！