Excel VBA能否按列字母循环遍历列？

我正在尝试基于多个单元格创建公式，为此写了一个获取单元格引用（如K2、M9这类格式）的函数。这个函数在单列场景下能正常运行，但需要循环遍历列的时候遇到了瓶颈。我查了不少资料，发现现有列循环方法都是用单元格地址而非列字母，想请教下能不能按列字母来循环遍历列？

附上我当前的VBA代码：

Function qscAddr(Sku As String, LineType As String, Customer As String, Column As String) As String
    Dim X As Long
    If IsNull(Customer) Then
        For X = 2 To Cells(Rows.Count, "A").End(xlUp).Row
            If Cells(X, "B").Value = Sku And Cells(X, "J").Value = LineType Then
                qscAddr = Column & X
                Exit Function
            End If
        Next
    Else
        For X = 2 To Cells(Rows.Count, "A").End(xlUp).Row
            If Cells(X, "B").Value = Sku And Cells(X, "J").Value = LineType And Cells(X, "I").Value = Customer Then
                qscAddr = Column & X
                Exit Function
            End If
        Next
    End If
End Function

Sub Form_Load()
    Dim c_xxx As String
    Dim c_yyy As String
    Dim c_zzz As String
    Dim Cell As Range
    
    Range("B3:B6500").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AG3"), Unique:=True
    
    For Each Cell In Range("AG4:AG28").Cells
        c_xxx = qscAddr(Cell.Value, "Current Forecast - Customer", "XXX", "K")
        c_yyy = qscAddr(Cell.Value, "Current Forecast - Customer", "YYY", "K")
        c_zzz = qscAddr(Cell.Value, "Current Forecast - Customer", "ZZZ", "K")
        Range(qscAddr(Cell.Value, "XYZ", "", "K")).Select
        Selection.Formula = "=" & c_xxx & "+" & c_yyy & "+" & c_zzz & ""
        
    Next Cell
    
End Sub

解决方案 #

完全可以按列字母循环遍历列，以下是两种实用方法：

方法1：直接遍历列字母数组 #

把需要循环的列字母放到一个数组里，遍历数组即可，适合明确知道要循环哪些列的场景。

修改你的Form_Load子过程，比如要循环K、L、M三列，代码示例：

Sub Form_Load_WithColumnLoop()
    Dim c_xxx As String, c_yyy As String, c_zzz As String
    Dim Cell As Range
    Dim colLetters As Variant
    Dim colLetter As Variant
    
    ' 定义要循环的列字母数组
    colLetters = Array("K", "L", "M")
    
    Range("B3:B6500").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AG3"), Unique:=True
    
    ' 遍历列字母
    For Each colLetter In colLetters
        ' 遍历每个SKU
        For Each Cell In Range("AG4:AG28").Cells
            c_xxx = qscAddr(Cell.Value, "Current Forecast - Customer", "XXX", colLetter)
            c_yyy = qscAddr(Cell.Value, "Current Forecast - Customer", "YYY", colLetter)
            c_zzz = qscAddr(Cell.Value, "Current Forecast - Customer", "ZZZ", colLetter)
            
            ' 避免使用Select，直接操作单元格提升效率
            Range(qscAddr(Cell.Value, "XYZ", "", colLetter)).Formula = "=" & c_xxx & "+" & c_yyy & "+" & c_zzz
        Next Cell
    Next colLetter
End Sub

方法2：列字母与列号互转后循环 #

如果需要连续列循环（比如从K到P列），可以先把起始列字母转成列号，循环列号后再转回列字母，适合范围列的场景。

需要新增两个辅助函数实现列字母和列号的互转：

' 列号转列字母
Function ColNumToLetter(colNum As Long) As String
    Dim div As Long
    Dim modu As Long
    ColNumToLetter = ""
    Do While colNum > 0
        div = (colNum - 1) \ 26
        modu = (colNum - 1) Mod 26
        ColNumToLetter = Chr(modu + 65) & ColNumToLetter
        colNum = div
    Loop
End Function

' 列字母转列号
Function ColLetterToNum(colLetter As String) As Long
    ColLetterToNum = Range(colLetter & 1).Column
End Function

然后修改循环逻辑，比如循环K到P列（列号11到16）：

Sub Form_Load_WithContinuousColumns()
    Dim c_xxx As String, c_yyy As String, c_zzz As String
    Dim Cell As Range
    Dim startCol As Long, endCol As Long
    Dim currentColNum As Long
    Dim currentColLetter As String
    
    ' 定义起始和结束列字母，转成列号
    startCol = ColLetterToNum("K")
    endCol = ColLetterToNum("P")
    
    Range("B3:B6500").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AG3"), Unique:=True
    
    ' 遍历列号，再转回列字母
    For currentColNum = startCol To endCol
        currentColLetter = ColNumToLetter(currentColNum)
        
        For Each Cell In Range("AG4:AG28").Cells
            c_xxx = qscAddr(Cell.Value, "Current Forecast - Customer", "XXX", currentColLetter)
            c_yyy = qscAddr(Cell.Value, "Current Forecast - Customer", "YYY", currentColLetter)
            c_zzz = qscAddr(Cell.Value, "Current Forecast - Customer", "ZZZ", currentColLetter)
            
            Range(qscAddr(Cell.Value, "XYZ", "", currentColLetter)).Formula = "=" & c_xxx & "+" & c_yyy & "+" & c_zzz
        Next Cell
    Next currentColNum
End Sub

额外优化建议 #

• 避免使用Select和Selection，直接操作单元格对象，能大幅提升代码运行效率。
• 给qscAddr函数添加错误处理，比如当找不到匹配项时返回空字符串或特定标识，避免后续代码报错。

内容的提问来源于stack exchange，提问作者K C