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

[keleding]

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.

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

Sub addCummulative()

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!

 

[R2ah1ze1l]

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]

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]

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.