# 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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### 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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,012
Messages
5,834,914
Members
430,326
Latest member
tomwax46

### 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