Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a column
Help please !!
Last week I posted this as follows; ""I would like to search a column of data for 'part characters' : eg user enters "lock" and macro searches column A and comes up with "block" in cell A6 and "locket" in cell A9. I am thinking this might incorporate the use of the LEN command, but without writing an immense amount of code I am sure there must be a more simpler and more efficient method."" And I received the following reply; "You will need to set SearchValue to the user input. With Range("A:A") Set rng = .Find(SearchValue, LookIn:=xlValues, LookAt:=xlPart)"" Well I have been really struggling ! Can anyone please send me a brief example of how I approach the code Ever so grateful Robert Pollock. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a column
If you're new to VBA then...
Generally, you'll use standard code modules to save your code. To get to a standard module open the IDE (Alt+F11) the in the project ad a standard module ... OR create a simple macro using th emacro recorder. Then, under too macros...got to the macro you recorded by clicking on the Edit button. This will take you to a standard module that Excel will have added for you. Now you should be able to add a "macro" or procedure yourself... Public Sub FindCells() ' you code End Sub I suspect that your reult could be achived using several other methods. For example, you could use conditional formatting to highlight cells that contain certain letters. You could use an AutoFilter to show cells containing that text or you use an Advanced filter to copy rows to another location. HTH Patrick Molloy Microsoft Excel MVP -----Original Message----- Help please !! Last week I posted this as follows; ""I would like to search a column of data for 'part characters' : eg user enters "lock" and macro searches column A and comes up with "block" in cell A6 and "locket" in cell A9. I am thinking this might incorporate the use of the LEN command, but without writing an immense amount of code I am sure there must be a more simpler and more efficient method."" And I received the following reply; "You will need to set SearchValue to the user input. With Range("A:A") Set rng = .Find(SearchValue, LookIn:=xlValues, LookAt:=xlPart)"" Well I have been really struggling ! Can anyone please send me a brief example of how I approach the code Ever so grateful Robert Pollock. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching a column
Just to add, look at the help example for the FindNext method using help in
VBA. -- Regards, Tom Ogilvy "Patrick Molloy" wrote in message ... If you're new to VBA then... Generally, you'll use standard code modules to save your code. To get to a standard module open the IDE (Alt+F11) the in the project ad a standard module ... OR create a simple macro using th emacro recorder. Then, under too macros...got to the macro you recorded by clicking on the Edit button. This will take you to a standard module that Excel will have added for you. Now you should be able to add a "macro" or procedure yourself... Public Sub FindCells() ' you code End Sub I suspect that your reult could be achived using several other methods. For example, you could use conditional formatting to highlight cells that contain certain letters. You could use an AutoFilter to show cells containing that text or you use an Advanced filter to copy rows to another location. HTH Patrick Molloy Microsoft Excel MVP -----Original Message----- Help please !! Last week I posted this as follows; ""I would like to search a column of data for 'part characters' : eg user enters "lock" and macro searches column A and comes up with "block" in cell A6 and "locket" in cell A9. I am thinking this might incorporate the use of the LEN command, but without writing an immense amount of code I am sure there must be a more simpler and more efficient method."" And I received the following reply; "You will need to set SearchValue to the user input. With Range("A:A") Set rng = .Find(SearchValue, LookIn:=xlValues, LookAt:=xlPart)"" Well I have been really struggling ! Can anyone please send me a brief example of how I approach the code Ever so grateful Robert Pollock. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching only one column in Excel | Excel Discussion (Misc queries) | |||
Searching up a column | Excel Discussion (Misc queries) | |||
Searching One Column Only | Excel Discussion (Misc queries) | |||
Searching a Column | Excel Programming | |||
Searching In a Column | Excel Programming |