时间戳

首页 > 解决方案 > 当我到达 excel 中的双字母时，我的列循环停止

excel - 当我到达 excel 中的双字母时，我的列循环停止

问题描述

我正在尝试遍历 24 列数据并获取特定信息，当我到达 AA 列时，在 Excel 中，我的代码停止了。我不明白为什么？

Sub Resultas_ELEC()
Dim iChar As Integer, i, j, k, l, m, n, a
a = 71     'colums searching in; 65==A, 71 == G ,    94==AD
For iChar = 71 To 94
i = Chr(iChar)
j = Chr(a)
k = Chr(a)
l = Chr(a)
m = Chr(a)
n = Chr(a)
Range(i & "77") = ActiveSheet.Application.WorksheetFunction.Countif(Range(i & "7:" & i & "73"), "=S")
Range(i & "78") = ActiveSheet.Application.WorksheetFunction.Countif(Range(j & "7:" & j & "73"), "=E")
Range(i & "79") = ActiveSheet.Application.WorksheetFunction.Countif(Range(k & "7:" & k & "73"), "=E-S")
Range(i & "80") = ActiveSheet.Application.WorksheetFunction.Countif(Range(l & "7:" & l & "73"), "=E-E")
Range(i & "81") = ActiveSheet.Application.WorksheetFunction.Countif(Range(m & "7:" & m & "73"), "=E-A")
Range(i & "82") = ActiveSheet.Application.WorksheetFunction.Countif(Range(n & "7:" & n & "73"), "=A")
a = a + 1
Next iChar
End Sub

标签： excelvba

解决方案

Use Cells()which 允许使用数字列引用。

Sub Resultas_ELEC()
    Dim iChar As Long
    
    With ActiveSheet 'should set this to the correct worksheet instead of relying on activesheet
    
        For iChar = 7 To 30
            .Cells(77, iChar) = ActiveSheet.Application.WorksheetFunction.CountIf(.Range(.Cells(7, iChar), .Cells(73, iChar)), "=S")
            .Cells(78, iChar) = ActiveSheet.Application.WorksheetFunction.CountIf(.Range(.Cells(7, iChar), .Cells(73, iChar)), "=E")
            .Cells(79, iChar) = ActiveSheet.Application.WorksheetFunction.CountIf(.Range(.Cells(7, iChar), .Cells(73, iChar)), "=E-S")
            .Cells(80, iChar) = ActiveSheet.Application.WorksheetFunction.CountIf(.Range(.Cells(7, iChar), .Cells(73, iChar)), "=E-E")
            .Cells(81, iChar) = ActiveSheet.Application.WorksheetFunction.CountIf(.Range(.Cells(7, iChar), .Cells(73, iChar)), "=E-A")
            .Cells(82, iChar) = ActiveSheet.Application.WorksheetFunction.CountIf(.Range(.Cells(7, iChar), .Cells(73, iChar)), "=A")
        Next iChar
    End With
End Sub

推荐阅读