需求:
当工作表中的列数比较多,而且列数、列的位置经常变动时,如果在VBA代码中使用的静态指定的列名,将不得不经常改动VBA代码,非常不方便。
解决方法:
使用自定义列号、列名查找函数。
代码如下:
'返回列字母
Function GetColumnLetterByHeader(headerText As String, WorksheetName As String) As String
Dim ws As Worksheet
Dim headerRange As Range
Dim regEx As New RegExp
' 设置正则表达式以匹配任何数字字符
regEx.Pattern = "\d"
regEx.Global = True ' 匹配字符串中的所有匹配项,而非第一个匹配项
' 初始化返回值
GetColumnLetterByHeader = ""
' 尝试获取工作表
On Error Resume Next
Set ws = ThisWorkbook.Worksheets(WorksheetName)
On Error GoTo 0 ' 恢复正常的错误处理
' 检查是否成功获取了工作表
If ws Is Nothing Then
MsgBox "未找到工作表:" & WorksheetName
Exit Function
End If
' 在工作表的第一行中查找列标题
Set headerRange = ws.Rows(1).Find(headerText, LookIn:=xlValues, LookAt:=xlWhole)
' 检查是否找到了列标题
If Not headerRange Is Nothing Then
' 直接返回列字母
GetColumnLetterByHeader = regEx.Replace(Split(headerRange.Address(False, False), "$")(0), "")
Else
MsgBox "未找到列标题:" & headerText
End If
End Function
'返回列标题所在的列的数字
Function GetColumnNumberByHeader(headerText As String, WorksheetName As String) As Integer
Dim ws As Worksheet
Dim headerRange As Range
' 初始化返回值,以防找不到列标题或工作表
GetColumnNumberByHeader = 0
' 尝试获取工作表
On Error Resume Next
Set ws = ThisWorkbook.Worksheets(WorksheetName)
On Error GoTo 0 ' 恢复正常的错误处理
' 检查是否成功获取了工作表
If ws Is Nothing Then
MsgBox "未找到工作表:" & WorksheetName
Exit Function
End If
' 在工作表的第一行中查找列标题
Set headerRange = ws.Rows(1).Find(headerText, LookIn:=xlValues, LookAt:=xlWhole)
' 检查是否找到了列标题
If Not headerRange Is Nothing Then
' 返回列的数字(注意:VBA中的列索引是从1开始的)
GetColumnNumberByHeader = headerRange.Column
Else
MsgBox "未找到列标题:" & headerText
End If
End Function
调用:
=GetColumnByHeader(“标标题”,“工作表名”)