excel vba-nesped for .. next循环比较两个值 - 在范围内的所有值中都不循环

Question

我正在努力根据一个符号选择（上/向下箭头，均等符号）自动化一个宏，该宏是根据一个值向上/向上移动a的四分位数或在季度中停留在同一季度。

我每个为每个循环使用三个。我认为处理此问题的正确方法是将第一个循环通过第一个旧值（例如A1）运行，然后将其存储在分配其四分位数号码的适当的UDF存储桶中。循环结束后，将新的循环运行使用相同的过程。一旦分配了两个值，第三个循环将比较两个四分位数数字，然后进行符号输入。然后，它将重新开始并循环穿过指定范围内的所有单元格。

。

循环看起来正确迭代，但似乎并未击中范围内的所有值。

我认为我的问题是循环设置的方式。有人知道一种更好的处理方法吗？

理想情况下，代码在B列中查看第一个值，并分配了" Current Quart"变量A值，然后循环到A列中并分配" OldQuart"变量A值A值。完成此操作后，第三个循环比较两个值并根据操作员输入字符。

函数，存储在模块中：

函数四极管（Byval CellValue作为变体）为布尔 Quartone =（CellValue> = 0.01和CellValue＆lt; = 25） 结束功能

Function quartTwo(ByVal cellValue As Variant) As Boolean
    quartTwo = (cellValue >= 25.01 And cellValue <= 50)
End Function
Function quartThree(ByVal cellValue As Variant) As Boolean
    quartThree = (cellValue >= 50.01 And cellValue <= 75)
End Function
Function quartFour(ByVal cellValue As Variant) As Boolean
    quartFour = (cellValue > 75)
End Function

代码

sub commandbutton1_click（）

Dim cellOld As Range, cellCurrent As Range, cell As Range
Dim oldRng1
Dim currentRng1 As Range
Dim oldQuart As Integer
Dim currentQuart As Integer

Set oldRng1 = ActiveSheet.Range("A1:A4")
Set currentRng1 = ActiveSheet.Range("B1:B4")

    For Each cellCurrent In currentRng1.Cells
            For Each cellOld In oldRng1.Cells
                    For Each cell In currentRng1.Cells
                    'checks cellCurrent against functions in module and assigns variable
                        If quartOne(cellCurrent.Value) Then
                            currentQuart = 1
                        ElseIf quartTwo(cellCurrent.Value) Then
                            currentQuart = 2
                        ElseIf quartThree(cellCurrent.Value) Then
                            currentQuart = 3
                        ElseIf quartFour(cellCurrent.Value) Then
                            currentQuart = 4
                        Else
                        End If

                    'checks cellOld against functions in module and assigns variable
                                 If quartOne(cellOld.Value) Then
                                        oldQuart = 1
                                ElseIf quartTwo(cellOld.Value) Then
                                        oldQuart = 2
                                ElseIf quartThree(cellOld.Value) Then
                                        oldQuart = 3
                                ElseIf quartFour(cellOld.Value) Then
                                        oldQuart = 4
                                Else
                                End If

                        'takes variable from above loops, runs through if/else and inputs corresponding character
                            If currentQuart = 1 And oldQuart = 1 Then
                                cell.Offset(, 1).Value = ChrW(&H3D)
                            ElseIf currentQuart = 1 And oldQuart > 1 Then
                                cell.Offset(, 1).Value = ChrW(&H2191)
                            ElseIf currentQuart = 2 And oldQuart < 2 Then
                                cell.Offset(, 1).Value = ChrW(&H2193)
                            ElseIf currentQuart = 2 And oldQuart = 2 Then
                                cell.Offset(, 1).Value = ChrW(&H3D)
                            ElseIf currentQuart = 2 And oldQuart > 2 Then
                                cell.Offset(, 1).Value = ChrW(&H2191)
                            ElseIf currentQuart = 3 And oldQuart > 3 Then
                                cell.Offset(, 1).Value = ChrW(&H2191)
                            ElseIf currentQuart = 3 And oldQuart = 3 Then
                                cell.Offset(, 1).Value = ChrW(&H3D)
                            ElseIf currentQuart = 3 And oldQuart < 3 Then
                                cell.Offset(, 1).Value = ChrW(&H2193)
                            ElseIf currentQuart = 4 And oldQuart < 4 Then
                                cell.Offset(, 1).Value = ChrW(&H2191)
                            ElseIf currentQuart = 2 And oldQuart = 4 Then
                                cell.Offset(, 1).Value = ChrW(&H3D)
                            End If
                            Exit For
                Next cell
             Next cellOld
     Next cellCurrent

End Sub

！测试数据在A-B列中存储为单个值（1-100）

您运行太多循环。

只需使用一个循环标准循环一次，然后按行比较每个单元格。

Sub CommandButton1_Click()
Dim i As Long
Dim oldRng1 As Range
Dim currentRng1 As Range
Dim oldQuart As Integer
Dim currentQuart As Integer

Set oldRng1 = ActiveSheet.Range("A1:A4")
Set currentRng1 = ActiveSheet.Range("B1:B4")

For i = 1 To currentRng1.Rows.Count
    'checks cellCurrent against functions in module and assigns variable
    If quartOne(currentRng1(i, 1).Value) Then
        currentQuart = 1
    ElseIf quartTwo(ccurrentRng1(i, 1).Value) Then
        currentQuart = 2
    ElseIf quartThree(currentRng1(i, 1).Value) Then
        currentQuart = 3
    ElseIf quartFour(currentRng1(i, 1).Value) Then
        currentQuart = 4
    Else
    End If
    'checks cellOld against functions in module and assigns variable
    If quartOne(oldRng1(i, 1).Value) Then
        oldQuart = 1
    ElseIf quartTwo(oldRng1(i, 1).Value) Then
        oldQuart = 2
    ElseIf quartThree(oldRng1(i, 1).Value) Then
        oldQuart = 3
    ElseIf quartFour(oldRng1(i, 1).Value) Then
        oldQuart = 4
    Else
    End If
    'takes variable from above loops, runs through if/else and inputs corresponding character
    If currentQuart = 1 And oldQuart = 1 Then
        currentRng1(i, 1).Offset(, 1).Value = ChrW(&H3D)
    ElseIf currentQuart = 1 And oldQuart > 1 Then
        currentRng1(i, 1).Offset(, 1).Value = ChrW(&H2191)
    ElseIf currentQuart = 2 And oldQuart < 2 Then
        currentRng1(i, 1).Offset(, 1).Value = ChrW(&H2193)
    ElseIf currentQuart = 2 And oldQuart = 2 Then
        currentRng1(i, 1).Offset(, 1).Value = ChrW(&H3D)
    ElseIf currentQuart = 2 And oldQuart > 2 Then
        currentRng1(i, 1).Offset(, 1).Value = ChrW(&H2191)
    ElseIf currentQuart = 3 And oldQuart > 3 Then
        currentRng1(i, 1).Offset(, 1).Value = ChrW(&H2191)
    ElseIf currentQuart = 3 And oldQuart = 3 Then
        currentRng1(i, 1).Offset(, 1).Value = ChrW(&H3D)
    ElseIf currentQuart = 3 And oldQuart < 3 Then
        currentRng1(i, 1).Offset(, 1).Value = ChrW(&H2193)
    ElseIf currentQuart = 4 And oldQuart < 4 Then
        currentRng1(i, 1).Offset(, 1).Value = ChrW(&H2191)
    ElseIf currentQuart = 2 And oldQuart = 4 Then
        currentRng1(i, 1).Offset(, 1).Value = ChrW(&H3D)
    End If
Next i

End Sub