VBA for comparing data within a range for Excel 2003

  • Thread starter Thread starter jmberner
  • Start date Start date
J

jmberner

Hello,

I have a column of data ( Column AD) where the cells will either be blank or
have a numerical value in the cell. There is no pattern to which a cell will
have a value or not.

I am attempting to write code that will compare the data in the cells.

In my example let D = the active cell
B = the cell with a numerical value 2 above the active cell
C = the cell with a numerical value 1 above the active cell
A = the cell with a numerical value 3 above the active cell

The logic is IF D >B, AND C>A Then AE = "Uptrend" or IF D<B, C<A, Then AE =
"DownTrend"

Everything else should be blan

What the data looks like:
ColumnAD
Row 1 3.4
Row 2
Row 3
Row 4 2.9
Row 5
Row 6
Row 7 3.1
Row 8 3.4
Row 9
Row 10 3.5

What I am aiming for
ColumnAD Column AE
Row 1 2.2
Row 2
Row 3
Row 4 2.9
Row 5
Row 6
Row 7 3.1
Row 8 3.4 Uptrend
Row 9
Row 10 3.5 Uptrend

I have started writing the code,but I am stuck. Here is what I have so fa
---------------------------------------------------------------------------------
Dim cell As Range
Set Range1 = Range(Range("AD2"), Range("AD65536").End(xlUp))

Range1.Select

For Each cell In Range1
If ActiveCell = "" Then
ActiveCell.Offset(0, 1).Value = ""
ElseIf ActiveCell <> "" Then
''IF ActiveCell > B AND C>A = UPTREND
''IF ActiveCell < B AND C<A = Downtrend

ActiveCell.Offset(0, 1).Value = 4

Else: ActiveCell.Offset(0, 1).Value = ""

End If
---------------------------------------------------------------------------


Any help that can be provided will be very much appreciated.

Thank you in advance.
 
Hi

You don't need vba to solve this one. A formula will work just as
well. Put this in the appropriate cell and drag it down.

=IF(ISBLANK(D2),"",IF((AND((D2<B2),(C2<A2))),"Uptrend","Downtrend"))

VBA will do the job if you do not want to drag your formula to the
bottom of the range each time.

Take care

Marcus

Sub Fill()
Dim lw As Integer

lw = Range("D" & Rows.Count).End(xlUp).Row
Range("E2").AutoFill Range("E2:E" & lw)
End Sub
 
Thanks Marcus,

The formula would work, but the problem is that appearance of a value in
column AD is completely random, so using the IF(AND(D2<B2),(C2<A2) does not
work accurately.

Jeff
 
Back
Top