Loop without using Macro if possible

[jkam61]

Dear friends,

I'm trying to resolve the following problem in my excel worksheet. Any help would be greatly appreciated.

I have the following numbers in column A (range is A1:A11):

<table width="64" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 15pt;" height="20"><td style="height: 15pt; width: 48pt;" width="64" align="right" height="20">10</td> </tr> <tr style="height: 15.75pt;" height="21"> <td style="height: 15.75pt;" align="right" height="21">11</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">12</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">11</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">13</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">13</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">13</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">12</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">12</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">10</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">11</td> </tr> </tbody></table>
I need to enter the following conditions in column B (Range B1:B11):

Starting at current cell:

1. If the value of the cell above is lower than current cell enter 1 in column B.
2. If cell above is greater than current cell, enter -1.
3. This is the tricky part. If cell above equals to current cell, go and check until you find a cell above that is not equal to current cell and then repeat 1 and 2.

The end result looks like this:

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" align="right" height="20">10</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td style="height: 15.75pt;" align="right" height="21">11</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">12</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">11</td> <td align="right">-1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">13</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">13</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">13</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">12</td> <td align="right">-1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">12</td> <td align="right">-1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">10</td> <td align="right">-1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">11</td> <td align="right">1</td> </tr> </tbody></table>
I will need to do it for thousands of rows, so using bunch of "IF" statements won't do it.

Thank you,
Jay

 

[pedie]

I just wrote and tried once hope this works.
Try

[/FONT]
[FONT=Courier New]Sub try()
Dim i As Long
For i = 2 To 11
  If Range("A" & i).Value > Range("A" & i).Offset(-1, 0).Value Then
   Range("B" & i).Value = "-1"
  ElseIf Range("A" & i).Value < Range("A" & i).Offset(-1, 0).Value Then
   Range("B" & i).Value = "1"
  ElseIf Range("A" & i).Value = Range("A" & i).Offset(-1, 0).Value Then
   Range("A" & i).Select
   Do Until ActiveCell.Value <> Range("A" & i).Offset(-1, 0).Value
   ActiveCell.Offset(0, 1).Select
   Loop
   ActiveCell.Value = "Here"
   Else
   End If
Next i[/FONT]
[FONT=Courier New]End Sub

 

[jkam61]

Thank you. I'm new to excel. I understand that this is a macro. Could you explain to me how to implement this, so I can see if it works? Thanks.

 

[jkam61]

Or if you could send the file you tried to my yahoo email: jkam61, it would be greatly appreciated. Thanks.

 

[Peter_SSs]

Couldn't you just use this formula in B2 and copy it down?

Excel Workbook
	A	B
1	10
2	11	1
3	12	1
4	11	-1
5	13	1
6	13	1
7	13	1
8	12	-1
9	12	-1
10	10	-1
11	11	1
Formula

 

[jkam61]

Thank you very much, Peter. I couldn't thank you enough. The simplicity of your solution makes you a true genius in my eyes. I greatly appreciate your help.