Arguments in If statements

[dap521]

Im trying to find all the max and the min in the data set by the slopes and for some reason my if statement wont work


If 0 > ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And 0 < ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
Cells(i, 2).Font.Color = vbBlue And Range("A" & i + 1).Value = Range("D" & X).Value And Range("B" & i + 1).Value = Range("E" & X).Value And X = X + 1
ElseIf 0 <= ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And 0 >= ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
Cells(i, 2).Font.Color = vbRed And Range("A" & i).Value = Range("F" & X).Value And Range("B" & i).Value = Range("G" & X).Value And X = X + 1
End If

can some one help me

 

[Fishboy]

Im trying to find all the max and the min in the data set by the slopes and for some reason my if statement wont work


If 0 > ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And 0 < ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
Cells(i, 2).Font.Color = vbBlue And Range("A" & i + 1).Value = Range("D" & X).Value And Range("B" & i + 1).Value = Range("E" & X).Value And X = X + 1
ElseIf 0 <= ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And 0 >= ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
Cells(i, 2).Font.Color = vbRed And Range("A" & i).Value = Range("F" & X).Value And Range("B" & i).Value = Range("G" & X).Value And X = X + 1
End If

can some one help me

Hi dap521, welcome to the boards.

Can you be a little more specific by "doesn't work"? Does it break and give an error message or does the code run but not give you the expected results? I have a suspicion that you are trying to cram as much in to a single line of code as possible which may or may not be causing your problems.

Until you respond with some more details it will be hard to judge however does this slightly tweaked version of your code still give you issues?

If 0 > ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And _
0 < ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
    Cells(i, 2).Font.Color = vbBlue
    Range("A" & i + 1).Value = Range("D" & x).Value
    Range("B" & i + 1).Value = Range("E" & x).Value
    x = x + 1
ElseIf 0 <= ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And _
0 >= ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
    Cells(i, 2).Font.Color = vbRed
    Range("A" & i).Value = Range("F" & x).Value
    Range("B" & i).Value = Range("G" & x).Value
    x = x + 1
End If

 

[dap521]

Hi dap521, welcome to the boards.

Can you be a little more specific by "doesn't work"? Does it break and give an error message or does the code run but not give you the expected results? I have a suspicion that you are trying to cram as much in to a single line of code as possible which may or may not be causing your problems.

Until you respond with some more details it will be hard to judge however does this slightly tweaked version of your code still give you issues?

If 0 > ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And _
0 < ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
    Cells(i, 2).Font.Color = vbBlue
    Range("A" & i + 1).Value = Range("D" & x).Value
    Range("B" & i + 1).Value = Range("E" & x).Value
    x = x + 1
ElseIf 0 <= ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And _
0 >= ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
    Cells(i, 2).Font.Color = vbRed
    Range("A" & i).Value = Range("F" & x).Value
    Range("B" & i).Value = Range("G" & x).Value
    x = x + 1
End If

Thank you for responding,
it has no errors messages, it wont change the color of the font or put the numbers in a new cell, its as if theres no faults in the error but it just wont excite the then statement. I don't know if that makes sense

 

[Fishboy]

Thank you for responding,
it has no errors messages, it wont change the color of the font or put the numbers in a new cell, its as if theres no faults in the error but it just wont excite the then statement. I don't know if that makes sense

OK, so are you able to share the macro in full rather than just a snippet? Obviously "x" and "i" are defined somewhere earlier in the code.
Ideally if you can share a copy of your workbook it will make it much easier to work out what the code is supposed to be doing. If you can share the workbook then you would need to upload a copy to a file hosting site such as Drop Box, One Drive, Google Drive or similar, then share a link to the file in a forum post.

 

[dap521]

this is the code

I don't have a specific data set I just put in random numbers to test it out.
if you want a data set

1 2
2 3
3 4
4 2
5 1
6 6
7 3
8 7
9 3
10 4
11 6
12 8
13 9
14 12
15 23
16 27
17 23
18 6
19 0
20 3

Sub MaxAndMin()


Dim i As Long
Dim min As Long
Dim max As Long
Dim X As Long
Dim N As Long

X = 2
N = 2

mycount = Application.CountA(Range("B:B"))
Range("C1").Value = mycount
mystop = mycount - 2
Range("C2").Value = mystop

Range("C3").Value = ((Cells(2, 2).Value - Cells(1, 2).Value) / (Cells(2, 1).Value - Cells(1, 1).Value))

For i = 1 To mystop



If 0 > ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And 0 <= ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
 Cells(i, 2).Font.Color = vbBlue And Range("A" & i + 1).Value = Range("D" & N).Value And Range("B" & i + 1).Value = Range("E" & N).Value And N = N + 1
 ElseIf 0 < ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And 0 >= ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
 Cells(i, 2).Font.Color = vbRed And Range("A" & i).Value = Range("F" & X).Value And Range("B" & i).Value = Range("G" & X).Value And X = X + 1
End If

Next i

End Sub

[code/]

 

[Fishboy]

This seems to work with your example data:

Sub MaxAndMin_New()


Dim i As Long
Dim min As Long
Dim max As Long
Dim X As Long
Dim N As Long


X = 2
N = 2


mycount = Application.CountA(Range("B:B"))
Range("C1").Value = mycount
mystop = mycount - 2
Range("C2").Value = mystop


Range("C3").Value = ((Cells(2, 2).Value - Cells(1, 2).Value) / (Cells(2, 1).Value - Cells(1, 1).Value))


For i = 1 To mystop


    If 0 > ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And _
        0 < ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
            Cells(i, 2).Font.Color = vbBlue
            Range("A" & i + 1).Value = Range("D" & X).Value
            Range("B" & i + 1).Value = Range("E" & X).Value
            X = X + 1
    ElseIf 0 <= ((Cells(i + 1, 2).Value - Cells(i, 2).Value) / (Cells(i + 1, 1).Value - Cells(i, 1).Value)) And _
        0 >= ((Cells(i + 2, 2).Value - Cells(i + 1, 2).Value) / (Cells(i + 2, 1).Value - Cells(i + 1, 1).Value)) Then
            Cells(i, 2).Font.Color = vbRed
            Range("A" & i).Value = Range("F" & X).Value
            Range("B" & i).Value = Range("G" & X).Value
            X = X + 1
    End If


Next i


End Sub