时间戳

首页 > 解决方案 > VBA无限嵌套for循环

excel - VBA无限嵌套for循环

问题描述

我正在尝试编写嵌套的 for 循环来循环遍历列的行以进行一些计算，然后转到下一列再次执行此操作。逻辑对我来说是有意义的，但是工作表的输出在正确答案之间来回闪烁并用相同的数字覆盖所有内容，并且它只是继续这样做。如果我需要澄清任何事情，请让我知道，提前谢谢。


Sub findAvg2()
Dim maxVal As Double
Dim preHr As Double
Dim nextHr As Double
Dim cVal As Double
Dim pVal As Double
Dim nVal As Double
Dim avg As Double
Dim maxAvg As Double
Dim i As Integer 'row
Dim j As Integer 'col
Dim lRow As Integer
Dim lCol As Integer

lRow = Cells(Rows.count, 1).End(xlUp).row                    'Find the number of rows in column A(1)
lCol = Cells(1, Columns.count).End(xlToLeft).Column

For i = 19 To lRow
    For j = 2 To lCol
        maxVal = Cells(2, j).Value
        preHr = Cells(8, j).Value
        nextHr = Cells(9, j).Value
        avg = (maxVal + preHr + nextHr) / 3
        If Cells(i, j).Value > 0 Then
            pVal = Cells(i - 1, j).Value
            cVal = Cells(i, j).Value
            nVal = Cells(i + 1, j).Value
            maxAvg = (pVal + cVal + nVal) / 3
            If avg > maxAvg Then
                maxAvg = avg
            End If
        End If
        Cells(12, j).Value = maxAvg
        'Debug.Print maxAvg
    Next j
Next i

End Sub

标签： excelvba

解决方案

我查看了您的代码，并没有发现任何问题。我所做的修改在我看来是具有装饰性的。这是结果。

Sub findAvg2()
    ' 005
    Dim maxVal As Double
    Dim preHr As Double
    Dim nextHr As Double
    Dim cVal As Double
    Dim pVal As Double
    Dim nVal As Double
    Dim Avg As Double
    Dim maxAvg As Double
    Dim Cl As Long                              ' last used column
    Dim Rl As Long                              ' last used row
    Dim C As Long                               ' column
    Dim R As Long                               ' row

    ' Find the number of used columns and roaws in the sheet
    Cl = Cells(1, Columns.Count).End(xlToLeft).Column
    Rl = Cells(Rows.Count, 1).End(xlUp).Row

    For R = 19 To Rl
        For C = 2 To Cl
            maxVal = Cells(2, C).Value
            preHr = Cells(8, C).Value
            nextHr = Cells(9, C).Value
            maxAvg = (maxVal + preHr + nextHr) / 3

            cVal = Cells(R, C).Value
            If cVal > 0 Then
                pVal = Cells(R - 1, C).Value
                nVal = Cells(R + 1, C).Value
                Avg = (pVal + cVal + nVal) / 3
                If Avg > maxAvg Then maxAvg = Avg
            End If
            Cells(12, C).Value = maxAvg
            'Debug.Print maxAvg
        Next C
    Next R
End Sub

您的这行代码可能存在弱点。For R = 19 To Rl. 由于您在计算平均值时包括了前一行，因此第 18 行必须包含数据。如果不是，并且您不能从评估中排除第一个数据行，则必须对初始 maxAvg 的计算做出特殊规定。

所有动作都发生在 ActiveSheet 上。这是我本能地不喜欢的安排。除非您从该工作表上的按钮调用 sub - 即便如此，如果智能 alec 想要使用 F5 代替 - 我会在代码中命名工作表。使用 CodeName 既可以提高安全性，又可以让用户自由地重命名工作表。此代码将在恰好处于活动状态的任何工作表上运行。它甚至不必在同一个工作簿中。

推荐阅读