| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard with constant
I have two sheets, one has part names of companies on and one has th full names on. I'm trying to replace the part names with the full name by setting a part name to a constant then searching for it in the othe sheet, then copying it over. However I can't find a way of using th find function with a constant and a wildcard entry. Any ideas? Cod below. Dim swop As String swop = ActiveCell Sheets("Customer List").Select Range("A2").Select Cells.Find(What:=swop"*", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Application.CutCopyMode = False Selection.Copy Sheets("Sheet1 (2)").Select ActiveSheet.Past -- fugfu ----------------------------------------------------------------------- fugfug's Profile: http://www.excelforum.com/member.php...fo&userid=2495 View this thread: http://www.excelforum.com/showthread.php?threadid=38713 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wildcard with constant
Try:
What:=swop & "*" fugfug wrote: I have two sheets, one has part names of companies on and one has the full names on. I'm trying to replace the part names with the full names by setting a part name to a constant then searching for it in the other sheet, then copying it over. However I can't find a way of using the find function with a constant and a wildcard entry. Any ideas? Code below. Dim swop As String swop = ActiveCell Sheets("Customer List").Select Range("A2").Select Cells.Find(What:=swop"*", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Application.CutCopyMode = False Selection.Copy Sheets("Sheet1 (2)").Select ActiveSheet.Paste -- fugfug ------------------------------------------------------------------------ fugfug's Profile: http://www.excelforum.com/member.php...o&userid=24950 View this thread: http://www.excelforum.com/showthread...hreadid=387138 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wildcard with constant
Find does not use wildcards. It searches for exact matches or part matches by
changing the parameter LookAt:=xlPart ' or xlWhole -- HTH... Jim Thomlinson "fugfug" wrote: I have two sheets, one has part names of companies on and one has the full names on. I'm trying to replace the part names with the full names by setting a part name to a constant then searching for it in the other sheet, then copying it over. However I can't find a way of using the find function with a constant and a wildcard entry. Any ideas? Code below. Dim swop As String swop = ActiveCell Sheets("Customer List").Select Range("A2").Select Cells.Find(What:=swop"*", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Application.CutCopyMode = False Selection.Copy Sheets("Sheet1 (2)").Select ActiveSheet.Paste -- fugfug ------------------------------------------------------------------------ fugfug's Profile: http://www.excelforum.com/member.php...o&userid=24950 View this thread: http://www.excelforum.com/showthread...hreadid=387138 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wildcard with constant
But xlPart will find "asdf" in both:
asdfqwer and qwerasdf (and I think find will support the wildcard.) Jim Thomlinson wrote: Find does not use wildcards. It searches for exact matches or part matches by changing the parameter LookAt:=xlPart ' or xlWhole -- HTH... Jim Thomlinson "fugfug" wrote: I have two sheets, one has part names of companies on and one has the full names on. I'm trying to replace the part names with the full names by setting a part name to a constant then searching for it in the other sheet, then copying it over. However I can't find a way of using the find function with a constant and a wildcard entry. Any ideas? Code below. Dim swop As String swop = ActiveCell Sheets("Customer List").Select Range("A2").Select Cells.Find(What:=swop"*", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Application.CutCopyMode = False Selection.Copy Sheets("Sheet1 (2)").Select ActiveSheet.Paste -- fugfug ------------------------------------------------------------------------ fugfug's Profile: http://www.excelforum.com/member.php...o&userid=24950 View this thread: http://www.excelforum.com/showthread...hreadid=387138 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wildcard with constant
I'll be darned you can use wildcards! I always used xlPart or xlWhole... And
once again I learn something new... -- HTH... Jim Thomlinson "Dave Peterson" wrote: Try: What:=swop & "*" fugfug wrote: I have two sheets, one has part names of companies on and one has the full names on. I'm trying to replace the part names with the full names by setting a part name to a constant then searching for it in the other sheet, then copying it over. However I can't find a way of using the find function with a constant and a wildcard entry. Any ideas? Code below. Dim swop As String swop = ActiveCell Sheets("Customer List").Select Range("A2").Select Cells.Find(What:=swop"*", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Application.CutCopyMode = False Selection.Copy Sheets("Sheet1 (2)").Select ActiveSheet.Paste -- fugfug ------------------------------------------------------------------------ fugfug's Profile: http://www.excelforum.com/member.php...o&userid=24950 View this thread: http://www.excelforum.com/showthread...hreadid=387138 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wildcard with constant
Please ignore my follow up post to you.
Jim Thomlinson wrote: I'll be darned you can use wildcards! I always used xlPart or xlWhole... And once again I learn something new... -- HTH... Jim Thomlinson "Dave Peterson" wrote: Try: What:=swop & "*" fugfug wrote: I have two sheets, one has part names of companies on and one has the full names on. I'm trying to replace the part names with the full names by setting a part name to a constant then searching for it in the other sheet, then copying it over. However I can't find a way of using the find function with a constant and a wildcard entry. Any ideas? Code below. Dim swop As String swop = ActiveCell Sheets("Customer List").Select Range("A2").Select Cells.Find(What:=swop"*", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Application.CutCopyMode = False Selection.Copy Sheets("Sheet1 (2)").Select ActiveSheet.Paste -- fugfug ------------------------------------------------------------------------ fugfug's Profile: http://www.excelforum.com/member.php...o&userid=24950 View this thread: http://www.excelforum.com/showthread...hreadid=387138 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to CF using a wildcard | Excel Discussion (Misc queries) | |||
| Constant loan payments vs. constant payments of principal | Excel Worksheet Functions | |||
| sum if wildcard | New Users to Excel | |||
| Wildcard | Excel Programming | |||
| Wildcard | Excel Programming | |||
![fugfug[_11_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
