title: Excel VBA- Nested For...Next loop to compare two values- Not looping through all values in range
body: <p>I'm working on putting together a macro that automates the manual selection of a symbol (up/down arrow, equal sign) based on whether or not a value has moved up/down a quartile or stayed the same quarter over quarter.</p>
  
  <p>I'm using three FOR EACH.... NEXT loops, each with it's own set of embedded if,elseif, else statements. I'm thinking the proper way to handle this is to have the first loop run through the first old value (A1 for example), and bucket it in the proper UDF bucket where it is assigned its quartile number. Once that loop ends, have the new loop run with the same procedures. Once both values have been assigned, the third loop will compare the two quartile numbers and then make the symbol input. It will then start over and loop through all of the cells in the specified range until all are set.</p>
  
  <p>The loop looks to be iterating correctly, but it doesn't seem to hit all values in the range.</p>
  
  <p>I think my issue is with the way the loops are set up. Does anyone know a better way to handle this?</p>
  
  <p>Ideally, the code looks at the first value in column b and assigns the 'currentQuart' variable a value, followed by looping into column a and assigning the 'oldQuart' variable a value. Once that is done, the third loop compares the two values and inputs a character based on the operator.</p>
  
  <p><strong>Functions, stored in module:</strong></p>
  
  <p>Function quartOne(ByVal cellValue As Variant) As Boolean
          quartOne = (cellValue >= 0.01 And cellValue &lt;= 25)
      End Function</p>
  
  <pre><code>Function quartTwo(ByVal cellValue As Variant) As Boolean
      quartTwo = (cellValue &gt;= 25.01 And cellValue &lt;= 50)
  End Function
  
  Function quartThree(ByVal cellValue As Variant) As Boolean
      quartThree = (cellValue &gt;= 50.01 And cellValue &lt;= 75)
  End Function
  
  Function quartFour(ByVal cellValue As Variant) As Boolean
      quartFour = (cellValue &gt; 75)
  End Function
  </code></pre>
  
  <p><strong>Code</strong></p>
  
  <p>Sub CommandButton1_Click()</p>
  
  <pre><code>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(&amp;H3D)
                              ElseIf currentQuart = 1 And oldQuart &gt; 1 Then
                                  cell.Offset(, 1).Value = ChrW(&amp;H2191)
                              ElseIf currentQuart = 2 And oldQuart &lt; 2 Then
                                  cell.Offset(, 1).Value = ChrW(&amp;H2193)
                              ElseIf currentQuart = 2 And oldQuart = 2 Then
                                  cell.Offset(, 1).Value = ChrW(&amp;H3D)
                              ElseIf currentQuart = 2 And oldQuart &gt; 2 Then
                                  cell.Offset(, 1).Value = ChrW(&amp;H2191)
                              ElseIf currentQuart = 3 And oldQuart &gt; 3 Then
                                  cell.Offset(, 1).Value = ChrW(&amp;H2191)
                              ElseIf currentQuart = 3 And oldQuart = 3 Then
                                  cell.Offset(, 1).Value = ChrW(&amp;H3D)
                              ElseIf currentQuart = 3 And oldQuart &lt; 3 Then
                                  cell.Offset(, 1).Value = ChrW(&amp;H2193)
                              ElseIf currentQuart = 4 And oldQuart &lt; 4 Then
                                  cell.Offset(, 1).Value = ChrW(&amp;H2191)
                              ElseIf currentQuart = 2 And oldQuart = 4 Then
                                  cell.Offset(, 1).Value = ChrW(&amp;H3D)
                              End If
                              Exit For
  
                  Next cell
               Next cellOld
       Next cellCurrent
  
  
  End Sub
  </code></pre>
  
  <p><strong>!Test Data is stored as single values (1-100) in column a-b!</strong></p>