Search Routine

Your example has rows with duplicate data, if you were to loop through the entire used range you would end up with duplicate rows copied to the other sheet.
This code will loop through Column B and copy and paste the found data to the other sheet.

Change the range to suite

It goes in the command button on the userform

Please Login or Register  to view this content.

hi all,

Dave's solution looks good so my suggestion's only adding a bit of variety...

Any of the following would need modification to some extent. Here are some other links in an old post of mine, which includes a link to a thread with an example Search Function file that may be quite useful for you.

hth
Rob

Originally Posted by mtclimber

...THis works good. However, I was hoping for something that was case sensitive and could recognize text inside of strings. Probably going to have to look at the find functions. Any sugestions.

Have you glanced at my links?
There is an argument/parameter in the code of my example file (see my previous post) that allows for case senstive searches.
You could adapt the code a lot, or, if you're happy to have users enter search strings directly onto the spreadsheet rather than via a textbox, you could almost use it "as is". Have a look at the file, try stepping through the code using [F8] & let me know if you have any questions...

Rob

hi mtclimber

Okie dokie, I'm going to try and go through this step by step - let me know if I miss anything or if I'm not clear enough. I'll start at the end because I really like the idea that you are giving it a go & learning

Originally Posted by mtclimber

Im just trying to get this to run and cant. It errors!

When something errors, it is important to say exactly what the error is & what line of code is highlighted. In this case, it is due to the fact that you aren't providing the required input argument (ie "Trgt" in the original file is defined as an argument in "SearchForInputsAndPaste(Trgt As Range)"). If you have good settings* in the VBE and type "(" after the name of a macro, you will see a wee popup that tells you what the (required or optional) arguments are.
You can partially test code validity before running it by typing "Option Explicit" at the top of each module and then using VBE - Debug - Compile. For a variety of other suggestions about VBA coding have a read of *http://www.cpearson.com/excel/DeclaringVariables.aspx & http://www.excelforum.com/excel-prog...id-in-vba.html.

-------
Ooopps, I got a bit side tracked - now, back to the start ...

Originally Posted by mtclimber

I have adapted the code to this demo. The original code used an on_change command to pull the searched for data from a cell on a sheet as a target value. I do not understand how this is passed to the sub.

The "Worksheet_Change" macro is an event driven macro that is "fired" everytime something on the particular worksheet is changed. Excel recognises which cell (s) are changed and passes them to the sub automatically which can be seen in the first line of the sub as "(ByVal Target As Excel.Range)". To identify different types of "event macros" for a specific sheet you can right click on a sheet tab in Excel & choose "View Code", this will bring up the sheet's code window in the VBE. At the top of the code window there should be two dropdown boxes, change the left one from "General" to "Worksheet" and then look through the list of available events shown under the righthand dropdown. Chip Pearson explains this much better (http://www.cpearson.com/excel/Events.aspx) and he also has a brief explanation about the different type of modules (http://www.cpearson.com/excel/codemods.htm).

-------------

Originally Posted by mtclimber

I also dont understand the "with Trgt" " Is Not" and "Is Nothing" stuff. I'm confused why the values being searched for appear to be ranges.

The string being searched for is the ".value" of a range (ie "Trgt" which is the cell that gets changed on the spreadsheet). I used the WorksheetChange event to identify what cell was filled out so that:
1) I didn't need to populate (& put the code behind) a userform.
2) I could use the ".value" as the search string.
3) & I could use the Trgt cell as the top left cell (eg "anchor") of the range when populating any Search results.

WITH statements are used when performing multiple actions (or referring to multiple properties) of an object such as a range, worksheet, workbook etc. Any object/property/method within a With Statement that is prefixed with a dot (".") becomes linked to the Object in the first line of the With statement. With statements make code shorter & more readable as well as potentially making it run faster because the object only needs to be resolved once.

However, when using the userform, you need another way of covering number3 (ie making an "anchor" for identifying where to put the search results).

Please Login or Register  to view this content.

*Here's a brief explanation of Is Nothing: http://www.dailydoseofexcel.com/arch...thing-keyword/

------------
I'm sorry I haven't gone through line by line this time - I'm hoping that you'll have another go & repost with some specific questions after checking out the Excel VBE Help files ([F1] key) and having a go at using my below (& above) suggestions. That will narrow down which lines of code you need further explanation about & save me some typing

Suggestions:
- change the code so the macro's first line is:

Please Login or Register  to view this content.

- add the below lines near the start of the SearchForInputsAndPaste macro:

Please Login or Register  to view this content.

- A more general suggestion is to be careful what you name your modules, macros and variables. To prevent any unexpected consequences, avoid using words that have a specific meaning in VBA - the example I spotted in your code & would change is your use of "Search" as a module name.

hth
Rob

hi Mtclimber,

Originally Posted by mtclimber

...But I don't understand why you need the case selects in the first place.

It's been a while since I wrote the code & I think I must have been going through a phase of using Select Case for everything!
(here are some examples of its flexibility)
I've read somewhere (?), that by ordering Select Case statements with the most common situation placed first, you can significantly speed up your code execution. However, in this case, where there is only a possibility of True or False, the use of Select Case probably comes down to (my) personal preference. for others' opinions have a read of http://www.ozgrid.com/forum/showthread.php?t=37379 (or http://www.ozgrid.com/forum/showthread.php?t=48628 or http://www.databison.com/index.php/v...ment-in-excel/ or for some timings http://www.ozgrid.com/forum/showthread.php?t=41814) for the opinions of a few people that are more knowledgeable than me.

Originally Posted by mtclimber

...I also don't understand "arng.parent" argument that sets up one of the case selects.

"arng.parent" refers to the "parent" of arng (aka "a range") & the parent of a range is the worksheet that the range is part of.
I'm guessing that you're referring to this section:

Please Login or Register  to view this content.

The use of "With aRng.Parent" statement allows for actions on properties of the worksheet (aRng.Parent) to be referred to with a dot prefix (see .range & .cells in the above code. This removes the need to fully qualify the reference each time it is used & can provide a nice visual clue of the groupings of actions in your code. There are a few sites that discuss the use of With statements but for the moment all I can find is http://www.cpearson.com/excel/optimize.htm.

I'm sorry about the delay & lack of details, please let me know if you need any more explanation...

Rob