Efficient If's loop
 

Hi all!

I have to test a column ("A") of numeric data and to categorize it (column
B) according to its value and or to the value of the cell below. For example,
if the value of A2 = 0 B2 value should be set to 1 , if the value of A2 < 0
B2 value should be set to 2. If the the value of A2 0 should be set to 4 if
A3 - A2 = 0 or A2 - A1 = 0 otherwise it should be set to 3.

I thought about the following code, but since the list at column A can reach
easialy 10000 cells, I am not sure that it is efficient.

x = 2

Do while Range("C" & lastrow) < 0
y = x + 1
z = x - 1

If Range("A" & x).Value = 0 Then
Range("B" & x).Value = 1
End If
If Range("A" & x).Value < 0 Then
Range("B" & x).Value = 2
End If
If Range("A" & y).Value - Range("A" & x).Value = 0 or Range("A" & x).Value
-Range("A" & z).Value = 0 Then
Range("B" & x).Value = 4
Else
Range("B" & x).Value = 3
End If

x = x + 1

Loop

Any suggestion for more efficient code?

Thanks in advance

Eli

Efficient If's loop
 

Maybe this

Sub stantial()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
If c.Offset(1, 0).Value - c.Value = 0 Then
c.Offset(, 1).Value = 3
ElseIf c.Value < 0 Then
c.Offset(, 1).Value = 0
ElseIf c.Value 0 Then
c.Offset(, 1).Value = 4
ElseIf c.Value = 0 Then
c.Offset(, 1).Value = 1
End If
Next

End Sub

Mike

"אלי" wrote:

Hi all!

I have to test a column ("A") of numeric data and to categorize it (column
B) according to its value and or to the value of the cell below. For example,
if the value of A2 = 0 B2 value should be set to 1 , if the value of A2 < 0
B2 value should be set to 2. If the the value of A2 0 should be set to 4 if
A3 - A2 = 0 or A2 - A1 = 0 otherwise it should be set to 3.

I thought about the following code, but since the list at column A can reach
easialy 10000 cells, I am not sure that it is efficient.

x = 2

Do while Range("C" & lastrow) < 0
y = x + 1
z = x - 1

If Range("A" & x).Value = 0 Then
Range("B" & x).Value = 1
End If
If Range("A" & x).Value < 0 Then
Range("B" & x).Value = 2
End If
If Range("A" & y).Value - Range("A" & x).Value = 0 or Range("A" & x).Value
-Range("A" & z).Value = 0 Then
Range("B" & x).Value = 4
Else
Range("B" & x).Value = 3
End If

x = x + 1

Loop

Any suggestion for more efficient code?

Thanks in advance

Eli