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

    Dim s As String, r As Range, c As Range
    
    s = TextBox1
    
    Application.ScreenUpdating = False

    Set r = Range("B2", Range("B65536").End(xlUp))
    
    For Each c In r.Cells
    
        If c = s Then
            c.Rows("1:1").EntireRow.Copy _
                    Destination:=Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0)
        End If
        
    Next c
    
    Unload Me

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).

    With Trgt
        iniBlankRow = .Row
'".row" in the above line of code could be written as "trgt.row"
'".value" in the below line of code could be written as "trgt.value" and this is used as the search string. The next line is attempting to Set a range variable based on any cells that are found when the FindAll function is processed. The Findall function may, or may not, actually find a result.
        Set ExactMatchingRng = FindAll(.Value, RngToSearch, LookAt:=xlWhole, ResizeToSevenCol:=True)
'the next line of code tests if a range (ie one or many cells) are found by the Findall function. I agree with you that this is hard to understand. I use this approach because it is reasonably common & I copied & pasted the code from somewhere quite a while ago! ;)
' The "ExactMatchingRng Is Nothing"* section checks if any object has been assigned to the ExactMatchingRng variable. this test is necessary because the code would error if you tried to use the ExactMatchingRng when no cells were found. Then the "Not" flips the result on its head (ie true becomes false & v.v.). I think this is just used so that the more "pro-active/positive" course of actions are placed in the first section of the If statement.
        If Not ExactMatchingRng Is Nothing Then
            ExactMatchingRng.Copy .Offset(0, 2)
            NumOfRows = LastCell(.Parent).Row + 1 - iniBlankRow
            .Offset(0, 1).Resize(NumOfRows).Value = "Exact match"
        Else
'this populates the cell one column to the right of the cell that was initially changed on the spreadsheet (ie the trgt. Note the dot prefix) with a comment...
            .Offset(0, 1).Value = "NO EXACT MATCHES"
        End If

*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:

Sub SearchForInputsAndPaste(SearchStr As String)

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

dim trgt as range
with ThisWorkbook.Worksheets("Original")
'this goes to the last row in column A of the spreadsheet and then comes up until it finds a cell with something in it. this is the equivalent of going to the last row on a spreadsheet & pressing [ctrl + [up arrow]]. The last section, "offset" then causes the cell immediately below the populated cell to become the focus & therefore be the definition of the Trgt variable for use in the "With Trgt" statement.
Set Trgt = .Cells(.Rows.Count, "a").End(xlUp).offset(1,0)
end with

- 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