Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The "Like" operator.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The "Like" operator.
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 -- HTH Bob Phillips "Spreadsheet Solutions" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The "Like" operator.
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... -- - K Dales "Spreadsheet Solutions" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
The "Like" operator.
? lcase("Lead MaNaGer Smith") Like "*manager*"
True -- Regards, Tom Ogilvy "Spreadsheet Solutions" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
The "Like" operator.
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. "Bob Phillips" wrote in message ... 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 -- HTH Bob Phillips "Spreadsheet Solutions" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem Solved !!!
Thanks to K Dales !!!
"Spreadsheet Solutions" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
The "Like" operator.
You are a genius.
Ask for a salary increase !!! I will act as a reference.... Mark. "K Dales" wrote in message ... 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... -- - K Dales "Spreadsheet Solutions" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
The "Like" operator.
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 -- Regards, Tom Ogilvy "Spreadsheet Solutions" wrote in message . .. 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. "Bob Phillips" wrote in message ... 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 -- HTH Bob Phillips "Spreadsheet Solutions" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
The "Like" operator.
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 & "*" -- steveB Remove "AYN" from email to respond "Spreadsheet Solutions" wrote in message . .. 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. "Bob Phillips" wrote in message ... 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 -- HTH Bob Phillips "Spreadsheet Solutions" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem Solved !!!
I think both Tom and I gave you that answer earlier, you just didn't look.
-- HTH Bob Phillips "Spreadsheet Solutions" wrote in message ... Thanks to K Dales !!! "Spreadsheet Solutions" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Comparison Operator "" NOT "=" | Excel Discussion (Misc queries) | |||
Is it possible to apply IF's to "operator" result.. | Excel Discussion (Misc queries) | |||
program stuck at "Save Cancelled by Operator" | Excel Discussion (Misc queries) | |||
Excel no longer allows new formulas with division "/" operator | Excel Programming |