The "Like" operator.

[Spreadsheet Solutions]

Dear all;

I have a front sheet were in a cell named txtrole one can enter a role (like
manager).
I want to search a column in a database on another sheet (Database) and find
all cells that contain the word manager.
So, manager qualifies, but lead manager also qualifies.
When a match is found, I want to copy the whole row to the first worksheet.

The code I developped -and which is not working- is written below.
Take into consideration that the code does start in the appropriate cell,
but that this part of the code is outside this sub.

Private Sub Check_02()
'Check Role
x2 = Worksheets("Front").Range("txtRole").Value
Do Until IsEmpty(ActiveCell)
If Application.Proper(ActiveCell.Offset(0, 4).Value) Like
Application.Proper(x2) Then
ActiveCell.EntireRow.Copy WF.Range("B20").Offset(i, -1)
i = i + 1
j = j + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

---

What must I change so that I can check how many cells qualify.
Do I use the like operator right or do I have to do this differently.

Mark.

 

[Bob Phillips]

Don't you just want

Private Sub Check_02()
'Check Role
x2 = Worksheets("Front").Range("txtRole").Value
Do Until IsEmpty(ActiveCell)
If ActiveCell.Offset(0, 4).Value Like "*manager*"
ActiveCell.EntireRow.Copy WF.Range("B20").Offset(i, -1)
i = i + 1
j = j + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[Guest]

If the user types in "manager" and your search is for "like 'manager'" then
it only finds the exact match, because there are no wildcards. If you want
to allow anything with "manager" in it, the proper form would be:
If Application.Proper(ActiveCell.Offset(0, 4).Value) Like "*" &
Application.Proper(x2) & "*" Then...

 

[Tom Ogilvy]

? lcase("Lead MaNaGer Smith") Like "*manager*"
True

 

[Spreadsheet Solutions]

Bob;

Thanks for your quick response.
I forgot to tell something.
x2 can be manager, but also partner.
So, partner and lead partner also have to qualify in case partner is
choosen.

Your code is correct, but only for manager.
My problem is: how does this work with a variable string.

Mark.

 

[Spreadsheet Solutions]

Thanks to K Dales !!!

 

[Spreadsheet Solutions]

You are a genius.
Ask for a salary increase !!!
I will act as a reference....

Mark.

 

[Tom Ogilvy]

Sorry, didn't realize you couldn't expand the solution on your own:

Private Sub Check_02()
'Check Role
x2 = Worksheets("Front").Range("txtRole").Value
Do Until IsEmpty(ActiveCell)
If Application.Proper(ActiveCell.Offset(0, 4).Value) Like _
"*" & Application.Proper(x2) "*" Then
ActiveCell.EntireRow.Copy WF.Range("B20").Offset(i, -1)
i = i + 1
j = j + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[STEVE BELL]

Should work just fine...

instead of x2 I might use str since x2 is also a range address

Dim X2 as String

X2= Worksheets("Front").Range("txtRole")

If ActiveCell.Offset(0, 4).Value Like "*" & X2 & "*"

 

[Bob Phillips]

I think both Tom and I gave you that answer earlier, you just didn't look.