Excel VBA-嵌套对...下一个循环来比较两个值 - 没有循环遍历范围内的所有值

Z时代
2024-01-10
分类：问答

我正在制作一个宏，可以自动手动选择一个符号（向上/向下箭头，等号）的基础上，价值是否上升/下降四分位或同一季度保持同一季度。Excel VBA-嵌套对...下一个循环来比较两个值 - 没有循环遍历范围内的所有值

我正在使用三个FOR EACH .... NEXT循环，每个循环都有自己的嵌套if，elseif，else语句。我正在考虑处理这个问题的正确方法是让第一个循环运行第一个旧值（例如A1），并将其分配到合适的UDF存储桶中，并在其中分配其四分位数。一旦该循环结束，使用相同的过程运行新循环。一旦两个值都被赋值，第三个循环将比较两个四分位数，然后进行符号输入。然后它将重新开始并遍历指定范围内的所有单元格，直到全部设置完成。

该循环看起来是正确迭代，但它似乎并没有达到范围内的所有值。

我想我的问题是与循环的方式设置。有没有人知道更好的方式来处理这个问题？

理想情况下，代码查看b列中的第一个值，并为'currentQuart'变量赋值，然后循环到列a中，并为'oldQuart'变量赋值。一旦完成，第三个循环会比较两个值并根据运算符输入一个字符。

函数，存储在模块：

功能quartOne（BYVAL cellValue为Variant）为布尔 quartOne =（cellValue> = 0.01和cellValue < = 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

！测试数据作为单个值（1-100）存储在列a-b！

回答：

您正在运行太多循环。

只需循环一次循环标准，并逐行比较每个单元格。

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