Posts by mikew7799

    Hey everyone!

    I am hoping someone can help me out with this issue or point me in the right direction of how I should go about doing this.

    Right now I have several sheets that each contain their own information. I have a listbox on my userform that is populated with the data from a specific sheet based on the value the user selects in the combobox (the value is the sheet name, hence that sheet is selected). I also have a search box that allows me to search the mastersheet (information from each individual sheet was collected and put into one mastersheet) for any cell that contains a partial match.

    What I would like to do is:

    1) Utilize the search box to find the specific item
    2) locate that item within the mastersheet AND the individual sheet it originally resided
    3) find the exact row and edit the value of column 5

    essentially I am allowing users to edit the room number that this item is moving to. I want them to click the item they're relocating, click edit, type in the new room its going to and have each sheet automatically update the correct cell.

    If anyone has any ideas that would be wonderful as I am still quite a beginner when it comes to coding any advice or input is welcomed and appreciated!


    Re: Search range, find any cell with partial match, select entire row then copy and p

    You are the man stephenR!

    Thank you for your help everything works exactly the way I wanted it to. If you wouldnt mind could you explain to me what you saw I did wrong and explain why you changed the location of certain lines? If so that would be wonderful, if not then i fully understand.

    One last question, if someone were to search "*#&%@" in the textbox the search function is never going to find anything because there isn't a cell that containing those characters. Instead, the userform freezes because it continues to search forever. Can i specify that if it doesnt contain any of those characters to display an error message and then exit sub, and can i also add a timeoout if it searches to long?

    Any ideas?


    the problem is I search "****" AKA anything that I know is not in my search range, and crange is somehow = "equipment" which should not be true but because I told it as along as it equals anything, continue down the list.

    This is the error produced, Im not sure how to fix this.

    in case anyone cannot read the error it says "run-time error 1004" & " unable to get the FindNext property of the range class"

    Thanks again for everyone's help!

    Re: Search range, find any cell with partial match, select entire row then copy and p

    Thanks for the cleanup I appreciate it! I'm still learning as I go so my code isn't the best nor is it the cleanest haha. As for the "for each" declaration no that does not need to be said that way. I wrote it that way because it was just what I thought would work at the moment. If you have a cleaner solution it would be greatly appreciate. In a nut shell all I want this code to do is:

    1) search the entire worksheet
    2) find any cells that contain a match or partial match (if you know of a way to exclude cells that only match 1 or 2 letters/number that would be great and restrict it more towards a whole rather than partial match)
    3) once the cell with a match is found, select that entire row
    4) either directly add that row to a listbox and continue searching for the next cell, or copy and paste that row to a new sheet (like I've been doing) and then specify the dynamic range to add to the listbox

    again, thank you, and everyone else for your/their input it is greatly appreciated!

    Re: Search range, find any cell with partial match, select entire row then copy and p

    Well I started from scratch because I got tired of messing around with other people code for my purposes and what I came up with is listed below. Everything works perfectly except that I cannot seem to get it to loop through. The loop i have setup now stops at the first cell that matches my search. I am not sure how to properly word the loop so that it loops through every cell and when it gets back to the first cell that matches the criteria (say "B2") I want it to exit the loop so it doesnt go back through the exact same cells twice

    Thanks again for all your help guys and girls,


    Hi everyone,

    Just recently joined this form and just started coding for vba about 2 weeks ago or so. My issue i am having is that i would like to search a sheet range ("A2:P1100") and any cell that contains a partial match (e.g. "the" in "weather") i would like that cells entire row (in this case i have information in every cell from A:P) to be selected and pasted to "sheet2".

    Once it has been pasted to another sheet I then want to specify it to populate a listbox that will dynamically change based on the number of rows i have filled in "sheet2"

    The listbox is no problem, the issue i am having is that I cannot seem to find a simple way to select the entire cells row based on the cell found. Below I have posted the code i am using and works perfectly except for the fact that It only takes the value of the cell; where i want it to find that cell, select the entire row, then take the value of every cell in that row. Any help or input would be greatly appreciated.

    Thank you in advance for everyone's input/help.