check if range does not contain certain text

[ira daboy]

I am trying to see if a range of cells has the word "shipping" before closing and i keep getting an error. below is what i have. can anyone help me out?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If WorksheetFunction.CountIf(Sheet2.Range("d2:d17") <> "shipping") Then
Cancel = False
MsgBox "Did you charge for shipping?"
End If
If Sheet6.Range("h1") = 2 And Sheet6.Range("f7") = "" Then
Cancel = True
MsgBox "You must enter an invoice date and a PO#."
End If

End Sub

 

[Joe4]

You have not stuctured the COUNTIF formula correctly. It has two arguments.
I think you want it structured something like this:

If WorksheetFunction.CountIf(range,"*shipping*")=0 Then

 

[ira daboy]

Hi Joe, I still get the same error when I use your syntax. Also how do I refer to a range on another sheet. Thanks for you help

 

[Joe4]

Hi Joe, I still get the same error when I use your syntax. Also how do I refer to a range on another sheet. Thanks for you help

Please post the updated version of the code you tried, and tell us exactly what the error message is.

Also, you are already prefacing your ranges with the sheet references. That is one way to reference other sheets (supposing you are using the correct value).

 

[ira daboy]

Here is my code: I just replaced the second line with what you suggested and the attached is the error i get. thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If WorksheetFunction.CountIf(Range, "*shipping*") = 0 Then
Cancel = False
MsgBox "Did you charge for shipping?"
End If
If Sheet6.Range("h1") = 2 And Sheet6.Range("f7") = "" Then
Cancel = True
MsgBox "You must enter an invoice date and a PO#."
End If
End Sub

 

[Joe4]

OK, you misinterpretted my initial reply.
I was posting how the statement was to be structured. Note how I put "range" in italics.
That was meant to just be a place holder for your actual range.
You need to enter/substitute a valid range in that spot (whatever range you actually want to run the COUNTIF function against).

 

[ira daboy]

Sorry to be a pain but this just isn't working for me. Below is my new code and I still get the same error. As well what I am trying to do is see if the word "shipping" doesn't" exist then remind me to charge for shipping.
thanks for you help with this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If WorksheetFunction.CountIf(Sheet2.Range, "d2:d17") = 0 Then
'Cancel = False
MsgBox "Did you charge for shipping?"
End If
If Sheet6.Range("h1") = 2 And Sheet6.Range("f7") = "" Then
Cancel = True
MsgBox "You must enter an invoice date and a PO#."
End If
End Sub

 

[Joe4]

What did you do here?

If WorksheetFunction.CountIf(Sheet2.Range, "d2:d17") = 0 Then

Why did you split your range up over two arguments, and totally get rid of the criteria?

Isn't this what you want?

If WorksheetFunction.CountIf(Sheet2.Range("d2:d17"), "*shipping*") = 0 Then

 

[ira daboy]

That worked perfect. Thanks for you patience Joe.

 

[Joe4]

You are welcome.
Glad we got it all working the way you need.