• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

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

excel 来源:Escott 6次浏览

我正在制作一个宏,可以自动手动选择一个符号(向上/向下箭头,等号)的基础上,价值是否上升/下降四分位或同一季度保持同一季度。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 

版权声明:本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。
喜欢 (0)