Syntax to identify if something does NOT match a Wildcard?

[ravl13]

Hi,

I'm writing a simple Excel macro that looks at the value of every cell in a column, and I know the following statement below is valid syntax to check whether the cell value is a valid government email:


If Selection.Value Like "*.gov" Or Selection.Value Like "*.mil" Then

However, I don't know how to write the opposite of that statement. In essence, I want to identify non-government emails. I tried doing this:


If Selection.Value Not Like "*.gov" And Selection.Value Not Like "*.mil" Then

But that does not work. How do I correctly write the above statement in valid VBA syntax?

Thank you for your time!

[jolivanes]

You could try

If Selection.Value<>"*.gov" And Selection.Value<>"*.mil" Then

[mancubus]

welcome to VBAx.


If Not


ps: i'm sure (:)) you use "selection" for illustrating the case here and you dont "select" or "activate" anything in your code.

[ravl13]

EDIT: Disregard this post. I've made a separate thread on this.

Jolivanes' suggestion wound up not doing what I wanted to do, but using If Not did the trick so thanks Mancubus.

I am extremely amateur - I don't even really know VBA syntax, but I know enough .NET syntax which is fairly similar that I can stumble around with VBA. This is actually the entire macro code following, and if anyone has pointers on how it could be improved, I'm open to best practice suggestions. The purpose of this macro is to go through every row, and delete all rows that do not have a government email. I'm sure there is a better way to identify the End of File than my patchwork loop condition, but I don't know the proper way to determine the end of a file


'Note that Column K must be the Email column for this macro to work
'Additionally, Column E should be City, and Column B should be either FirstName or LastName, for the End of File to be properly detected


'Code for the CombineIntoHouseholds Excel Macro
Sub DeleteNonGovtPeople()




'Specify which sheet we are working with in the workbook
Sheets("Sheet1").Select

'Clean all cells of excess whitespace
For Each cell In ActiveSheet.UsedRange
cell.Value = Trim(cell)
Next cell






'Select the first entry of the "K" (Email) column. K1 is the column header, so K2 would be the first entry
Range("K2").Select

'This loop will continue until the selected cell (which would be in the email column) and the
'column E (which would be in the City column) and Column B (FirstName) are all blank.
'Additionally, the row after that must also be blank in those fields in order for the loop to stop
Do Until Selection.Value = "" And Selection.Offset(0, -6).Value = "" And Selection.Offset(0, -9).Value = "" And Selection.Offset(1, 0).Value = "" And Selection.Offset(1, -6).Value = ""

'Identify whether it's a government email
If Not Selection.Value Like "*.gov" And Not Selection.Value Like "*.mil" Then

'Delete the entire Row if the Person has a govt email
Selection.EntireRow.Delete


Else

'Move the currently selected cell down one row
Selection.Offset(1, 0).Select


End If

Loop




End Sub


And here is a screenshot of my sample spreadsheet that I am running the code on, for context:

11980

[mancubus]

you are welcome.

providing a link to another thread would be helpful for future references.

so here it is:

http://www.vbaexpress.com/forum/showthread.php?50196-Optimizing-VBA-code-so-that-it-can-execute-on-a-workbook-of-50-000-rows-w-o-crashing

[ravl13]

You could try

If Selection.Value<>"*.gov" And Selection.Value<>"*.mil" Then

What I don't understand is if I run this code with jolivanes' suggestion:



Sub DeleteNonGovtPeople()




'Specify which sheet we are working with in the workbook
Sheets("Sheet1").Select

'Clean all cells of excess whitespace
For Each cell In ActiveSheet.UsedRange
cell.Value = Trim(cell)
Next cell






'Select the first entry of the "K" (Email) column. K1 is the column header, so K2 would be the first entry
Range("K2").Select

'This loop will continue until the selected cell (which would be in the email column) and the
'column E (which would be in the City column) and Column B (FirstName) are all blank.
'Additionally, the row after that must also be blank in those fields in order for the loop to stop
Do Until Selection.Value = "" And Selection.Offset(0, -6).Value = "" And Selection.Offset(0, -9).Value = "" And Selection.Offset(1, 0).Value = "" And Selection.Offset(1, -6).Value = ""

'Identify whether it's a government email
If Selection.Value <> "*.gov" Then

'Delete the entire Row if the Person has a govt email
Selection.EntireRow.Delete


Else

'Move the currently selected cell down one row
Selection.Offset(1, 0).Select


End If

Loop




End Sub

It deletes EVERY row, even if the email has a .gov extension. I found that the use of this line:


If Selection.Value<>"*.gov" And Selection.Value<>"*.mil" Then

is literally checking for an exact match of *.gov and *.mil, those five exact characters. How could that statement be properly written for wildcard checking while using the <> operator? Conceptually, Jolivanes' suggestion makes sense, but for some reason excel doesn't interpret it that way.

[GTO]

Oops, nevermind. I missed seeing post #4.

.Value = [some string] or .Value <> [some string] doesn't kick wildcards into gear.

[ravl13]

Oops, nevermind. I missed seeing post #4.

.Value = [some string] or .Value <> [some string] doesn't kick wildcards into gear.

So wildcards can't be used with equality operators... Boo and hiss, I say. Thanks for the info though