# How to compare values during a loop after a value is found

#### keleding

##### New Member
I hope someone could help me with both VBA and formula. As the picture shows below, Switch is when production# is increased or decreased from previous day. So, -1 is decrease and 1 is increase. For consecutive days of same switch, I calculated cumulative production # for each day. Now, the score column is where I don't know how to do in one formula or one VBA sub code. I yellow highlighted the section when switches changed on day 5 from day 4, the production# is 6131960, which is less than its previous day's (or previous cumulative section's) production # 13981549. So I give it a score 0. I move down to day6 to compare it to day 4, or 6, 9837810 vs. 13981549, and so on for each cell. On day 8, the 16954564 is greater than the 13981549, so I give it a score of -1, because this is in a decreasing period. I looked at this website many times. I could not figure out how to hold the 13981549 in memory and loop down to let each cell to compare it.

I am not good in VBA. My VBA for the Switch and Cumulative based on Production # is as follows. If anyone can suggest a better or more efficient coding that will be greatly appreciated. I know mine is way too amature.

VBA Code:
``````Sub addChangeSign()

Dim rng1 As Range
Dim c1 As Range
Dim lastrow As Long
Dim i As Long

lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Set rng1 = Range(Cells(2, 2), Cells(lastrow, 2))

For Each c1 In rng1

If c1 > c1.Offset(-1, 0) Then
c1.Offset(0, 1) = 1
ElseIf c1 < c1.Offset(-1, 0) Then
c1.Offset(0, 1) = -1

End If
Next
End Sub

Dim rng1 As Range
Dim c1 As Range
Dim lastrow As Long
Dim i As Long

lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Set rng1 = Range(Cells(2, 3), Cells(lastrow, 3))

For Each c1 In rng1
If c1 = c1.Offset(-1, 0) Then
c1.Offset(0, 1) = c1.Offset(0, -1) + c1.Offset(-1, 1)
ElseIf c1 <> c1.Offset(-1, 0) Then
c1.Offset(0, 1) = c1.Offset(0, -1)

End If
Next
End Sub``````

Thank you very much. Any suggestion and advice will be appreciated!

Last edited by a moderator:

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### R2ah1ze1l

##### Board Regular
I feel like you want to change from If to While to complete your goal.

While loop would let you hold your "13981549" value until complete with the objective.

I'm not following what is happening, just trying to go off your statement. Good Luck!

#### keleding

##### New Member
I feel like you want to change from If to While to complete your goal.

While loop would let you hold your "13981549" value until complete with the objective.

I'm not following what is happening, just trying to go off your statement. Good Luck!
Thank you for the tip. Will try that. Is there a way to use formula to do the while? thanks again!

#### R2ah1ze1l

##### Board Regular
Thank you for the tip. Will try that. Is there a way to use formula to do the while? thanks again!
Based on what I read, your condition will be due to the changed 1/0/-1 values.

Replies
2
Views
46
Replies
7
Views
136
Replies
2
Views
87
Replies
4
Views
84
Replies
14
Views
358

### Forum statistics

1,127,321
Messages
5,624,012
Members
416,005
Latest member
judi slot terbaik

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back