当工作表中的列数比较多,而且列数、列的位置经常变动时,如果在VBA代码中使用的静态指定的列名,将不得不经常改动VBA代码,非常不方便。
使用自定义列号、列名查找函数。
代码如下:
1. 列号查找函数
- '查找列号函数
- Private Function intFindColumnID(ByVal rowID, ByVal objworkBook, ByVal objWorkSheet, ByVal strColumnName) As Integer
- objworkBook.Activate
- objWorkSheet.Select
- objWorkSheet.Cells(1, 1).Select
- Cells.Find(What:=strColumnName, After:=ActiveCell, LookIn:=xlFormulas, _
- LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
- MatchCase:=False, SearchFormat:=False).Select
- If Selection.Row = rowID Then
- intFindColumnID = Selection.Column
- Else
- intFindColumnID = 0
- End If
- End Function
2. 调用查找列号函数
- '调用查找列号函数
- Sub findColumnID()
- Dim objworkBook As Workbook '声明工作簿变量
- Dim objWorkSheet As Worksheet '声明工作表变量
- Dim columnNumber As Integer '声明列号(列标)变量 (1-10384)
- Dim columnName As String '声明列名变量 (A-XFD)
- Dim targetColumnTitleName As String '声明要查找的列的列标题
- Set objworkBook = ThisWorkbook '指定工作簿
- Set objWorkSheet = objworkBook.Sheets("test") '指定工作表
- targetColumnTitleName = "Detailed Description" '指定要查找的列标题
- 'objWorkSheet.Range("I1").Value = intFindColumnID(1, objworkBook, objWorkSheet, targetColumnTitleName)
- 'objWorkSheet.Range("J1").Formula = "=Substitute(Address(1,I1, 4), ""1"", """")"
- columnNumber = intFindColumnID(1, objworkBook, objWorkSheet, targetColumnTitleName) '调用列号查找函数
- 'objWorkSheet.Range("J1").Formula = "=Substitute(Address(1," & columnNumber & ", 4), ""1"", """")" '4表示单元格引用方式4:relative row & relative column reference.
- columnName = Application.Evaluate("=Substitute(Address(1," & columnNumber & ", 4), ""1"", """")") '使用Substitute函数将类似"D1"这样得单元格地址中的1替换为空白字符(即,删除数字1,仅留下列名(字母A至XFD),对应1至10384)
- MsgBox (columnName)
- End Sub
运行结果示例:
联系客服