Beginner to VBA: Greater than

Question

I have numerical values entered in Row 1 from columns A to IA. I want to create a loop that compares one cell with the cell before it (aka Cell B1 to A1 or cell F to E). Let's use B1 and A1 as the example. It looks at the Value in Cell B1 and sees if it is greater then the value of the cell in A1. If it is greater then I want a + to be entered in the Cell B2. Also if B1 is < A1 put a - into Cell B2. I want the program to be able to loop this process so it does it for all the columns A-AI. 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).

        A        B        C        D        F
1       33.12    34.52    34.92    35.19    34.97
2                (+)      (+)      (+)      (-)

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!

Sub EnterFormula()

    Dim x As Integer
    Dim y As Integer

    x = Worksheets("Sheet2").Range("C2").Value
    y = Worksheets("Sheet2").Range("B2").Value

    If x > y Then
        Worksheets("Sheet2").Range("C4") = "+"
    End If

End Sub

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.

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.

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.

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.

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.

        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

Here is my code so far for this. I have added to my original code which was creating the "+" signs and "-" signs.

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

' Now get the value of the cell that is before it (column-wise) previousValue = Cell.Offset(0, -1).Value

' Create a variable for our target cell
Set targetCell = Cell.Offset(1, 0)

' Here are the basic comparisons
If currentValue > previousValue Then
    targetCell.Value = "+"
ElseIf currentValue < 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 <> previousSign.Value And oppositeSign.Value = currentSign.Value And currentNumericalCell.Value > oppositeNumericalCell.Value Then
targetSignCell = "U"
ElseIf currentSign.Value = "-" And currentSign.Value <> previousSign.Value And oppositeSign.Value = currentSign.Value And currentNumericalCell.Value < oppositeNumericalCell.Value Then
targetSignCell = "D"
Else
End If
Next Cell
End Sub

Answer 1

Assuming there are no empty cells in the range you want to work in, you could do it like this:

Range("b2").Select
Do Until IsEmpty(ActiveCell.Offset(-1, 0))
If ActiveCell.Offset(-1, 0).Value > ActiveCell.Offset(-1, 1).Value Then
ActiveCell.Formula = "+"
End If
If ActiveCell.Offset(-1, 0).Value < ActiveCell.Offset(-1, 1).Value Then
ActiveCell.Formula = "-"
End If
ActiveCell.Offset(0, 1).Select
Loop

If there are empty cells in the range then instead of 'do until' use

dim I 
for I = 1 to ..

next I

Answer 2

I agree with @JohnBustos that a formula would be much more efficient, however if this is indeed a learning exercise then here is a simple example that would do what you want:

Sub Comparison()

Dim targetCell As Range

' 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("B1:E1")

    ' Get the value of the current cell with the .Value property
    currentValue = cell.Value

   ' Now get the value of the cell that is before it (column-wise)
    previousValue = cell.Offset(0, -1).Value

    ' Create a variable for our target cell
    Set targetCell = cell.Offset(1, 0)

    ' Here are the basic comparisons
    If currentValue > previousValue Then
        targetCell.Value = "+"
    ElseIf currentValue < 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


End Sub

And if you were to do it as a formula, it could be something like this (entered into B2 and copied to the end of the range):

=IF(B1>A1,"+",IF(B1<A1,"-","="))

This compares the cell above the formula and the cell to the left of that cell and adds the appropriate symbol.