需求描述:
当工作表中的列数比较多,而且列数、列的位置经常变动时,如果在VBA代码中使用的静态指定的列名,将不得不经常改动VBA代码,非常不方便。
解决方法:
使用自定义列号、列名查找函数。
代码如下:
1. 列号查找函数
Private Function intFindColumnID(ByVal rowID, ByVal objworkBook, ByVal objWorkSheet, ByVal strColumnName) As Integer 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
2. 调用查找列号函数
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)
运行结果示例:
|