Kickbutt VBA Find Function

  • A Christmas treat for my OZgrid friends. :gift:


    Wraping the VBA Find/FindNext methods into a function you can use in your code to return found range objects!


    This function is awesome, and the uses are many...



    This should work in 2002 and later. You may have to tweak it a bit to work with earlier versions. For instance, I don't think SearchFormat is an option for the FIND method in 2000 and earlier. You can wipe that line if needed.



    Here's just a few of the potential uses for this function...


    Select all cells in a range that contain 22 as part of the value:

    Code
    1. Find_Range(22, Range("D10:G20")).Select


    Clear the range if the cell contains exactly 999, but if it's a formula leave it be:

    Code
    1. Find_Range(999, Range("D10:G20"), xlFormulas, xlWhole).ClearContents


    Delete all rows that contain "X" in column A:

    Code
    1. Find_Range("X", Columns("A"), MatchCase:=True).EntireRow.Delete


    Quickly scan the whole sheet if you like!

    Code
    1. Find_Range(1000, Cells, xlFormulas, xlWhole).EntireRow.Select



    How often have you seen people asking, "How do I find all rows that match a criteria and paste the results to a new sheet?" Now you can do it in a single line of code. For those who can appreciate it, this last one is pretty amazing!


    Copy all the rows that have the value 1000 in column D and paste to Sheet2:
    Find_Range(1000, Columns("D"), xlFormulas, xlWhole).EntireRow.Copy Range("Sheet2!A1")

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Kickbutt VBA Find Function


    And for the truly brave, you could even do a find/copy/paste-append to a different sheet as a one-liner!


    Find_Range(1000, Columns("D"), xlFormulas, xlWhole).EntireRow.Copy Range("Sheet2!D65536").End(xlUp).Offset(1, 0).EntireRow



    I tested this one on a 10,000 dataset and for 100 finds in the 10,000 block it was nearly instantaneous. On 65,536 records with 650 finds it took less than 5 seconds. :wow:



    This next one is a tad abstract, but if you need to perform the above operation and search multiple columns the one-liner above MIGHT (yes it depends) fail if I just changed the columns reference to "A:D". In this case, a union would take care of it.


    Set Found_Range = Find_Range(1000, Columns("A:D"), xlFormulas, xlWhole).EntireRow
    Union(Found_Range, Found_Range).Copy Range("Sheet2!A65536").End(xlUp).Offset(1, 0).EntireRow



    In this case, the union is resolving overlapping range issues created because a matching value was found in multiple columns of the same row. If there are no instances of the found item existing in multiple columns of the same row, this is not an issue. Notice for the above example this works:
    Union(Found_Range, Found_Range).Copy


    While this alone would fail:
    Found_Range.Copy


    That is, it would fail if there were multiple occurances of the value within the row.


    Similarly, you can imagine how you'd have to also use the union if you were returning entire columns with this function.


    Enjoy!
    -AB

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Kickbutt VBA Find Function


    Not really... it works just like the find feature on the menu toolbar. There are other VBA methods you can use to test cell values.



    Quote from jwaldon

    Is there anyway to use this with a number that is not fixed? Such as anything greater than 5000?


    Thanks,
    Josh

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Kickbutt VBA Find Function


    Hi Aaron, just a couple of things;


    1) FirstAddress wasn't defined.


    2) I like to use functions where you are given choices rather then guess @ the expected values i.e LookIn = xlValues 'xlFormulas the use wil be expected to places these constant in.


    To this end (For Excel2000+) I use Enum like so ......
    With Enum writing the Code gives the user intellisence options for the Consts.


    Don't take this the wrong way, it is a nice function... one that I will use :)


    Any way for xl2000+ this is how I will use it >


  • Re: Kickbutt VBA Find Function



    Good idea Ivan.
    I think I'll incorporate the Enum as well.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Kickbutt VBA Find Function


    Is there a quick one liner that would take out all the lines starting with a string (word?) or symbols ("----")? if so what should I put at the the place "what"?


    Find_Range(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)



    Thanks for the great code. I'll use it a lot!!!!

  • Re: Kickbutt VBA Find Function


    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Kickbutt VBA Find Function


    Code
    1. Sub Dele()


    Code
    1. Find_Range("-", Range("A1:A60000")).EntireRow.Delete


    Code
    1. Find_Range("Caepipe", Range("A1:A60000")).EntireRow.Delete



    Code
    1. End Sub



    I wrote a little sub to take away useless lines in files utilizing the Find_Range function. This function is great!!! When I run the code there's an error saying :
    Run-time error '91':
    Object variable or With Block variable not set


    What should I do? what's wrong?

  • Re: Kickbutt VBA Find Function


    I notice you're leaving the optional variables empty. That's fine if you use my original code, but you might get an error if you tried to use Ivan's enumeration suggestion without modifying the IsMissing tests to test for zero instead. I just now noticed his code didn't include that change.


    I liked Ivan's enumeration idea, so I updated the example on my website to include it. But because you redefine the input parameters as enumerations, they will never trip the IsMissing test. This is how I incorporated the enumeration suggestion.




    Also, in your code.

    Code
    1. Find_Range("-", Range("A1:A60000")).EntireRow.Delete


    I'd probably just refer to the entire column A in one of the following ways.

    Code
    1. Find_Range("-", Range("A:A")).EntireRow.Delete
    2. Find_Range("-", [A:A]).EntireRow.Delete
    3. Find_Range("-", Columns("A")).EntireRow.Delete

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Kickbutt VBA Find Function


    Quick question, I want the whole thing to run as a button click, do i put the whole code into the button? as in do i do the following


    If that is correct is said there was an error at

    Code
    1. SearchForma[B]t:[/B]=False)


    I couldnt understand why it came there?

  • Re: Kickbutt VBA Find Function


    Ivan - I've found a problem with your version of this code - under Xl2003, the tests for missing conditional-arguments aren't working.


    To get it to work, I had to change:




    Code
    1. Function Find_Range(Find_Item As Variant, _
    2. Search_Range As Range, _
    3. Optional LookIn As eLookin, _
    4. Optional LookAt As eLookat, _
    5. Optional MatchCase As Boolean) As Range


    to


    Code
    1. Function Find_Range(Find_Item As Variant, _
    2. Search_Range As Range, _
    3. Optional LookIn As Variant, _
    4. Optional LookAt As Variant, _
    5. Optional MatchCase As Boolean) As Range


    i.e. declare Lookin and LookAt as Variants.


    The original code compiled, but gave a subscript-out-of-range runtime error because both LookIn and LookAt remained at zero rather than being enumerated.


    HTH,
    MD7

  • Re: Kickbutt VBA Find Function



    Good catch!


    ...but if you look about 4 or 5 posts up the thread, you'll see I addressed this issue back in January.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Kickbutt VBA Find Function


    Hi guys I have a feeling this may help me with what i am trying to do but am not sure how to do it. I have a moderate understanding of vba. I am trying to import data from a file and if the ID in column B exists on sheet 2 then skip to next entry if it does not exist on sheet to then check sheet 3 if does not exist on sheet 3 then add to the bottom however if it exists i want to check column D and if the value of the import data exceeds the current data then replace the whole row. Can someone guide me with this? Thanks in advance