Msgbox with certain value

[JEANNYNEWBIE]

Hi, I am currently studying the VBA and trying out this on my file but i got runtime error..

In my worksheet "SHIPPED", range "C3:C600"
I would like to have a message pop up when the cell contains "CHECK"
Other text found this column varies so I wanted t have a msg alert when there is "CHECK" in this range.


Private Sub Check_Price()

If Range("C3:C6000").Value = "CHECK" Then
MsgBox "Error in Price", chr10, "Please check Shipping Instruction"
End If

End Sub

Please help.. thanks you so much!

 

[MARK858]

Maybe...

Private Sub Check_Price()
    Dim myCell As Range
    For Each myCell In Sheets("SHIPPED").Range("C3:C6000")
        If myCell.Value = "CHECK" Then
            MsgBox "Error in Price", chr10, "Please check Shipping Instruction"
            Exit Sub
        End If
    Next
End Sub

 

[My Aswer Is This]

So we are checking 600 cells. If the script finds 200 cells with Check you want the script to popup a message box 200 times. How will that help you?

You will not be able to do anything till the script has stopped.

 

[JEANNYNEWBIE]

Hi Mark858!
Tried your code and it worked!!!!
Except for [chr10,"please check shipping instructon"]

but thanks a lot!!!!

 

[My Aswer Is This]

Try this:

Sub Check_Price()
    'Modified  1/15/2019  1:45:41 AM  EST
    Dim myCell As Range
    For Each myCell In Sheets("SHIPPED").Range("C3:C6000")
        If myCell.Value = "CHECK" Then
            MsgBox "Error in Price" & Chr(10) & "Please check Shipping Instruction"
            Exit Sub
        End If
    Next
End Sub

 

[MARK858]

Except for [chr10,"please check shipping instructon"]

I am afraid that part was just copied/pasted from the code you originally posted and I didn't check that you had that line correct (my bad for not testing )

See post number 5 from My Aswer Is This for the correct syntax for that line.

Hi M.A.I.T. thanks for the correction.

 

[JEANNYNEWBIE]

Try this:

Sub Check_Price()
    'Modified  1/15/2019  1:45:41 AM  EST
    Dim myCell As Range
    For Each myCell In Sheets("SHIPPED").Range("C3:C6000")
        If myCell.Value = "CHECK" Then
            MsgBox "Error in Price" & Chr(10) & "Please check Shipping Instruction"
            Exit Sub
        End If
    Next
End Sub

Thank you!!!

 

[JEANNYNEWBIE]

Thanks!
I have another question, because I tried to modify and then insert Else, but I cannot end the macro by clicking OK Button.

Dim myCell As Range
For Each myCell In Sheets("SHIPPED").Range("C3:C6000")
If myCell.Value = "CHECK" Then
MsgBox "Please check Shipping Instruction and/or Invoice", chr10, "ERROR IN PRICE", vbOKOnly + vbExclamation
Exit Sub
'Else
'MsgBox "CHECKING FINISHED", vbOKOnly + vbInformation
End If
Next

End Sub

Again, advancing many thanks to you!

 

[MARK858]

Why do you need an Else statement?

    Dim myCell As Range
    For Each myCell In Sheets("SHIPPED").Range("C3:C6000")
        If myCell.Value = "CHECK" Then
            MsgBox "Error in Price" & Chr(10) & "Please check Shipping Instruction", vbOKOnly + vbExclamation
            Exit Sub
        End If
    Next
    MsgBox "CHECKING FINISHED", vbOKOnly + vbInformation
End Sub

And why did you change back to the msgbox code that erred?

 

[JEANNYNEWBIE]

Hi Mark858!

I wanted to add another msgbox for information when user click the button CHECK.

I did research on Msgbox contents and i modified it. The user chose this.