Wildcard in Excel VBA

[jeff.white]

I'm trying to use a search box in excel vba with a wildcard. My sheet
list about 1000 employees with among other things there job title.
What I'd to do is have the search box use a wildcard to search and
then delete those that do not match. The code I have so far is:

Sub DeleteRows()
Dim JobTitle, FinDate As Date, LastRow&, i&
MsgBox ("Use this routine will DELETE many rows in your Excel Sheet,
proceed with CAUTION!!")
JobTitle = InputBox("Enter Job Title Below - Caution ROWS will be
deleted!!!!!")
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 12).Value <> JobTitle & "*" Then
Rows(i).Delete
End If
Next i
End Sub

An example of a job title would be: Branch Manager, Branch Manager I,
Branch Manager II and so on. I would like enter: 'Branch' (without
the quote marks of course) and have all those job titles that start
with Branch remain while the other rows get deleted. As is, all rows
get deleted. Any ideas?

 

[JE McGimpsey]

One way:

If Cells(i, 12).Text Like JobTitle & "*" Then

 

[Joel]

try changing this line
from
If Cells(i, 12).Value <> JobTitle & "*" then
to
If left(Cells(i, 12).Value,len(JobTitle) <> JobTitle Then

 

[jeff.white]

One way:

If Cells(i, 12).Text Like JobTitle & "*" Then





- Show quoted text -

Thanks for the quick repsone JE, however your suggestion deletes the
rows I want to keep. I want to do the reverse. I've tried modifing
what you suggested to:

if cells(i,12).text LIKE NOT JobTitle & "*" Then....but the error
message I get is Type MisMatch...any ideas?

 

[Mike H]

Modified to delete everything except LIKE Branch....

Mike

Sub DeleteRows()
Dim JobTitle, FinDate As Date
MsgBox ("Use this routine will DELETE many rows in your Excel Sheet,proceed
with CAUTION!!")
JobTitle = InputBox("Enter Job Title Below - Caution ROWS will be
deleted!!!!!")
LastRow = ActiveSheet.Range("L65536").End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 12).Value Like JobTitle & "*" Then

Else
Rows(i).Delete
End If

Next i
End Sub

 

[JE McGimpsey]

Try:

If Not Cells(i, 12) Like JobTitle & "*" Then

 

[jeff.white]

One way:

If Cells(i, 12).Text Like JobTitle & "*" Then


Thanks for the quick repsone JE, however your suggestion deletes the
rows I want to keep. I want to do the reverse. I've tried modifing
what you suggested to:

if cells(i,12).text LIKE NOT JobTitle & "*" Then....but the error
message I get is Type MisMatch...any ideas?- Hide quoted text -

- Show quoted text -

Thanks to all...Jeol, I tried your suggestion and that seems to
work ...thanks again....

 

[Tom Ogilvy]

I guess you can count on your users to enter the Job title with the exact
capitalization. If not, you might want to account for that.

? Left("Branch Manager",len("branch")) = "branch"
False
? Left("Branch Manager",len("Branch")) = "Branch"
True



--
Regards,
Tom Ogilvy

 

[Peter T]

Could head a module dedicated for such string comparisons -

Option Compare Text

Regards,
Peter T