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

keleding

New Member
Joined
Dec 27, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

DowCLX20y.xlsm - Excel2021_01_088h573.jpg


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

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!
 
Last edited by a moderator:

Some videos you may like

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
Joined
Nov 10, 2017
Messages
67
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
Joined
Dec 27, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top