Need help with If statment, reading Cell Values.

[aragon123321]

Hello, What I want is for when a number in the column passes a value that I have set, a message will be sent to me.

The code that I have to send me a message is working its just not reading the values that I have in my columns correctly and sending me to many messages even when I have nothing that is passed my control limit.

Hope someone can help thanks so much, first post.


Here is my code:

Sub Check_Numbers()
Dim Cell As Range
On Error Resume Next
For Each Cell In ActiveSheet.Range("C3:C17")
If Cell.Value >= 4 Then Call Send_Messag

Next


Dim Cell As Range
On Error Resume Next
For Each Cell In ActiveSheet.Range("D3:D17")
If Cell.Value >= 7 Then Call Send_Messag

End If

Next

End Sub

 

[antialias401]

The way you posted should cause a VBA error due to the improper "End If" placement.
Other than that, it seems legit. You should try catching the values that are causing a a "false positive".

For Each Cell In ActiveSheet.Range("C3:C17")
      If Cell.Value >= 4 Then
          Msgbox Cell.Value & " >=4: Press OK to Call Macro ""Send_Meassg"""
          Call Send_Messag
     End if

and

For Each Cell In ActiveSheet.Range("D3:D17")
      If Cell.Value >= 7 Then
          Msgbox Cell.Value & " >=7: Press OK to Call Macro ""Send_Meassg"""
          Call Send_Messag
     End if

The Message Box is for testing, hopefully you can "catch" the numbers/characters that are causing you grief.
Hope this helps.

 

[aragon123321]

Thanks so much for the reply, really appreciate it!

I tried changing it that way.

I am still having the same problem.

If i want it to send me a alert when it passes 4, it will send me a message no matter what the number is.

This is what I want:
If any cell value in a certain range has a value of more than 4 then I want to Call another Sub that will send me a text message.

Is there any code that is better than this you think to help me do what I want to.

As it is now I am always going to my other sub that sends me a message.

Thank you!

 

[antialias401]

It seems that the message is coming up even when a LOWER value comes up, and is undesirable.
Your code should call the message as desired. I don't know how to improve with the info you've provided.

The message boxes I showed will help you help you catch why the Sub is being called.
Once you find the culprit, then you can communicate that and we can help you further.
In other words, your code should work fine, no visible errors that I can see (other than the aforementioned End if)

My guess is that you might be using Index/Match or Vlookup and the cells are updating before your final value settles in.
Install the code I provided, It will warn you when it fires, and it will give you the (offending) value, and you can find the culprit.