title: Beginner to VBA: Greater than
body: <p>I have numerical values entered in Row 1 from columns <code>A</code> to <code>IA</code>.  I want to create a loop that compares one cell with the cell before it (aka Cell <code>B1</code> to <code>A1</code> or cell <code>F</code> to <code>E</code>). Let's use <code>B1</code> and <code>A1</code> as the example. It looks at the Value in Cell <code>B1</code> and sees if it is greater then the value of the cell in <code>A1</code>. If it is greater then I want a <code>+</code> to be entered in the Cell <code>B2</code>.  Also if <code>B1</code> is <code>&lt; A1</code> put a <code>-</code> into Cell <code>B2</code>. I want the program to be able to loop this process so it does it for all the columns <code>A-AI</code>. Below is what I want want the program to do (not including the dashes and teh paranthesis around the positive and negative signs of course).</p>
  
  <pre>
          A        B        C        D        F
  1       33.12    34.52    34.92    35.19    34.97
  2                (+)      (+)      (+)      (-)
  </pre>
  
  <p>I realize this task is easily performed in excel (not using VBA) but I am trying to learn VBA so I can perform much more complex tasks. I have written the basic code to do the simple task but I am not sure how to loop it so it will do this for all my cells!</p>
  
  <pre><code>Sub EnterFormula()
  
      Dim x As Integer
      Dim y As Integer
  
      x = Worksheets("Sheet2").Range("C2").Value
      y = Worksheets("Sheet2").Range("B2").Value
  
      If x &gt; y Then
          Worksheets("Sheet2").Range("C4") = "+"
      End If
  
  End Sub
  </code></pre>
  
  <p>Ok So for the next part of My Program. It gets a touch more complicated. We move onto row 3. Row 3 is going to either have a U (for Up) or a D (for down) or nothing. </p>
  
  <p>Let's Start at Column C. Column C1 has a value of 34.92, and C2 was given a + (as 34.92 was larger then the day before which was 33.02).  Now we go to the first previous "+" WITH AT LEAST one opposite sign (in this case "-") in between. So in this case that is row A (one "-" inbetween under row B).  Now if the Numerical Value in C1 (34.92) is larger then the numerical value in A (33.12) then we designate a "U" in C3. If it was NOT larger we would leave an empty cell in C3.  </p>
  
  <p>Let's move onto column D. Column D1 has a value of 35.19 which is greater then the C1 value of 34.92 and this is why D2 has a "+". Next we go to the first previous "+" WITH AT LEAST one opposite sign (in this case "-") in between. So in this case that is row A again. Since the numerical value in D1 (39.19) is greater then the numerical value in A1 (33.12) then D3 gets a U.  </p>
  
  <p>Moving onto Column F (32.97)...Note:I changed the value a little from the original F. 32.97 is LESS then 35.19 (D1) which is why F2 is a "-". Next we go to the first previous "-" WITH AT LEAST one opposite sign (in this case "+") in between. So in this case this is Row B (with two "+" signs in between). Now because we are dealing with "-" signs this time we look and see if the numerical value in F1 is LESS then the numerical value in B1...which it is, so a "D" is entered in F3. If F1 was larger then B1 then the cell would be left empty.</p>
  
  <p>Onto Column G (35.21). This is greater then 32.97 (F1) and therefore gets a "+" in G2.  Next we go to the first previous "+" WITH AT LEAST one opposite sign in between (in this case "-"). So in this case this is Row D (with one "-" in between). Since the numerical value of G1 is greater then that of D1 we designate a "U". If it was not greater we would leave the cell empty.  </p>
  
  <pre>
          A        B        C        D        F        G        H        I
  1       33.12    33.02    34.92    35.19    32.97    35.21    35.60    35.90
  2       (+)      (-)      (+)      (+)      (-)      (+)      (+)      (+)
  3                          U        U        D        U        U        U
  </pre>     
  
  <p>Here is my code so far for this. I have added to my original code which was creating the "+" signs and "-" signs.</p>
  
  <pre><code>Sub Comparison()
  
      Dim targetCell As Range
      Dim targetSignCell As Range
      Dim currentSign As String
      Dim currentNumericalCell As Currency
  
      ' Find out what sign (+ or -) the current Cell has in it
      currentSign = Worksheets("Sheet2").Range("H3").Value
      'Variable to associate the numerical number above the current Cell
      currentNumericalCell = Worksheets("Sheet2").Range("H2").Value
  
      ' Here we iterate through each cell in a specified range
      ' Since you know you want to start at B1 and go until E1,
      ' you can ues the following syntax to go through each cell
      For Each Cell In Range("B2:H2")
  
      ' Get the value of the current cell with the .Value property
  currentValue = Cell.Value
  </code></pre>
  
  <p>' Now get the value of the cell that is before it (column-wise)
      previousValue = Cell.Offset(0, -1).Value</p>
  
  <pre><code>' Create a variable for our target cell
  Set targetCell = Cell.Offset(1, 0)
  
  ' Here are the basic comparisons
  If currentValue &gt; previousValue Then
      targetCell.Value = "+"
  ElseIf currentValue &lt; previousValue Then
      targetCell.Value = "-"
  ElseIf currentValue = previousValue Then
      targetCell.Value = "="
  Else
      ' Not sure how it would happen, but this
      ' is your catch-all in case the comparisons fail
      targetCell.Value = "???"
  End If
  
  ' Now go to the next cell in the range
  Next Cell
  
  'Alex starting to code
  For Each Cell In Range("H3:B3")
  ' Find out what the sign is in the cell before it
  previousSign = Cell.Offset(0, -1).Value
  'Variable used to find the first cell with an
  'Opposite sign as the current cell
  oppositeSign = Cell.Offset(0, -2).Value
  'Variable to associate the numberical number above the first Opposite Sign Cell
  oppositeNumericalCell = Cell.Offset(-1, -2).Value
  ' Create a Variable for Target Cell
  Set targetSignCell = Cell.Offset(1, 0)
  If currentSign.Value = "+" And currentSign.Value &lt;&gt; previousSign.Value And oppositeSign.Value = currentSign.Value And currentNumericalCell.Value &gt; oppositeNumericalCell.Value Then
  targetSignCell = "U"
  ElseIf currentSign.Value = "-" And currentSign.Value &lt;&gt; previousSign.Value And oppositeSign.Value = currentSign.Value And currentNumericalCell.Value &lt; oppositeNumericalCell.Value Then
  targetSignCell = "D"
  Else
  End If
  Next Cell
  End Sub
  </code></pre>