Trigger based on maxima and minima

artz · May 10th 2019

Hi Stephen, am glad that is not a problem. I will test when you send a new workbook file. Thanks, -Art

**StephenR** · May 10th 2019

You can play around with the colours if you're fussed.

Code

Sub x()
 
Dim r As Range, sAddr As String, n As Long, i As Long


n = Range("B" & Rows.Count).End(xlUp).Row


Range("I2:J" & n).Clear


With Range("C2:D" & n)
    Set r = .Find(What:="*", After:=Range("D" & n), Lookat:=xlWhole, SearchOrder:=xlByRows, _
                      SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not r Is Nothing Then
        sAddr = r.Address
        Do
          i = r.Row
          If r.Column = 3 Then
            Do Until Cells(i, 2) <= r.Offset(, 2)
                i = i + 1
            Loop
            Cells(i, 10) = Cells(i, 2)
            Cells(i, 10).Interior.Color = RGB(0, 255, 0)
           Else
            Do Until Cells(i, 2) >= r.Offset(, 2)
                i = i + 1
            Loop
            Cells(i, 9) = Cells(i, 2)
            Cells(i, 9).Interior.Color = RGB(255, 0, 0)
          End If
          Set r = .FindNext(r)
         Loop While r.Address <> sAddr
    End If
End With
     
End Sub

Display More

artz · May 10th 2019

Hi Stephen,

Great job! Spot on! I just got the chart working and it looks great.

Thanks so much for your help!

-Art

**StephenR** · May 10th 2019

Glad it worked Art.

artz · May 10th 2019

Hi Stephen,

For matching the original data points, your sub calculated the trigger points as expected. However, if I change the values in N2 and O2, and run your sub, the worksheet does not update the trigger points. Could you please take a look at this? I've attached the latest workbook.

Thanks,

-Art

artz · May 11th 2019

Hi Stephen,

In my last post I goof; sorry if I wasted your time needlessly. Your code doesn't call N2 and O2, my code does. On the attached workbook, I added a radio button which executes both my macros updating columns C,D,E, and F on button click. When I subsequently run your Sub(x) code, columns I and J don't return proper values.

Could you please take a look at the attached workbook?

Thanks,

-Art

**StephenR** · May 13th 2019

Works for me Art. When I run it I get

1.08295 in I30
1.07442 in J84

etc.

What values are you getting?

You can call the macros from each other so running one runs them all. (Also suggest you give mine a better name!)

artz · May 13th 2019

Hi Stephen, I get blank cells in I30 and J83. On the attached workbook, I've added a button to run your code. (and renamed the sub) When I tried to run all 3 subs from one button, Excel crashed. Here's what I've noticed also: If I open the workbook and change N2 or O2 and call your code using the On Off Points button, it works only once. Sometimes if I click again, Excel crashes. The debugger goes to your Do Until loop. If Excel doesn't crash, your code is not updating columns I and J. If you have a chance, could you take a look? Thanks, -Art

artz · May 14th 2019

Hi Stephen, trying to run the workbook again tonight several times, the workbook crashes consistently when I call your macro. Hopefully you have some ideas because I sure don"t. Thanks, Art

**StephenR** · May 14th 2019

This site is getting painful so everything is hard work at the moment. (Can't attach a file either.) It works for me on your attached file with 0.8 / 1.1. I noticed that with some settings its possible that a trigger point is not reached before the next min/max is reached and that can cause an error. Should it automatically stop in those cases? The code runs for me from the button.

artz · May 14th 2019

Hi Stephen, I've noticed that the website is slow to load sometimes and I also have had trouble uploading a file. Can you try varying the parameters on your side and see if the worksheet updates? As long as there are values in columns E and F, they should be in the range of column B data. Like I mentioned yesterday, your code runs only once at least on my two laptops Is there an alternative way to code this sub? -Art

artz · May 14th 2019

Hi Stephen, I think that I see whats going on. You alluded to this in your last post. If N2* last maxima < next minima or if O2 * last minima > next maxima, the code crashes. Could you add a check for this with a message "Value exceeds maxima" or "Value exceeds minima? Thanks, -Art

Trigger based on maxima and minima

Participate now!

Share