VBA code to find all rows containing a specified value RRS feed

  • Question

  • Greetings all!

    I'm sure I'm not alone in that I am very much on the learning end of VBA. Most of my coding experience comes from extremely simple and linear languages regulated by line numbers. So, rather than ask for a turnkey solution I prefer to break up my question(s) into bite size pieces so I can learn instead of being handed the finished project.

    Anyways, on this particular project I have already created a userform that tracks employees' time spent doing particular tasks. The information is copied into a separate sheet in that workbook and then copied into a separate "Master" workbook on the server as essentially an Excel database, never to be opened outside of maintenence. The data contained (my plan, anyway) will be accessed by other users' workbooks they have on their local machine. These "satellite" documents won't actually serve any storage purpose... only as a metrics/analysis platform to be emptied when the calculations or whatever is complete.

    This all works very well. However, when the people I work with and look at the wall of data, their eyes gloss over and they have no idea what to do with it. Given this and the fact I really don't want people directly using the "Master", I want to be able to find all cells within a column (in this case, column C) containing, for example, the number part# "54558" and copy those/that row into the ("satellite user's") workbook for calculation and whatnot. Seeing as there will be multiple rows containing the number 54558, I'd like to either find/copy/paste them all at the same time or loop find/copy/paste one at a time. I'll use a UserForm as the query platform and ultimately I'd like to apply this find/copy/paste method to all columns in the userform.

    What would be the syntax/command(s) to do this? I have attached an image of the "Master database" in question (ignore the far right hand side "Total time" column, it will be gone when I get this worked out). If it helps, the workbook name for the "Master" and its range of data is A1:P1500 and is named "Work Form Data.xlsx". The destination workbook will be "Calculation.xlsx" into Sheet1. I can't link or insert images yet else I would have done that to make things easier

    Thank you for any tips! :)

    Friday, March 15, 2013 4:05 PM

All replies

  • I started to write some instructions for you to do this in VBA. The code is relatively simple but I feel that it is too complex to explain without an example. (The old story; a picture is worth a thousand words and the same for VBA code; an example is also worth a thousand words.

    However, basically the code would follow the principle od setting AutoFilter on the Master data to display the required data and then copy the visible cells and paste them into the Calculate workbook.

    All of the code should be in a separate workbook and code used to open the Master and Calculate workbooks.

    If you want to see an example workbook then if you wish to post an email address then I will reply.

    Regards, OssieMac

    Saturday, March 16, 2013 6:07 AM
  • Should you accept my offer to communicate with email then please let me know what version of Excel you are using. It makes a difference on how part of it will be coded.

    Regards, OssieMac

    Saturday, March 16, 2013 11:44 AM
  • Attach link to file example using Skydrive on another.

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Saturday, March 16, 2013 1:11 PM
  • Attach link to file example using Skydrive on another.

    Oskar Shon, Office System MVP

    Skydrive does not allow me to upload a file with VBA code. If you believe I should be able to then pleases provide instructions.

    Regards, OssieMac

    Saturday, March 16, 2013 8:07 PM
  • Sorry Mac, I have many files with code in my skydrv.

    No problem to make link to them - anyway smaier69 can give us a zipped sample.

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Sunday, March 17, 2013 4:06 PM
  • Hello, guys :)

    I do appreciate the willingness to help. I made a temporary FTP share to host the file(s). I assume that you wanted an example of the Master data file? The data is arbitrary but the form structure (which data going into columns) is correct.

    ftp : // excelshare:msdn@

    The webpage here tells me I cannot post files or links in the body of the post so just remove the spaces at the beginning of the above ftp url.

    We are running Office/Excel 2010.

    Cheers! :)

    • Edited by smaier69 Monday, March 18, 2013 4:36 PM I forgot a
    Monday, March 18, 2013 3:51 PM
  • Sub fExample()
        Const csQuery As String = "Query"
        Dim sPartNumber As String
        Dim rFind As Range
        Dim rRows As Range
        Dim rData As Range
        Dim sFirstMatch As String
        Dim ws As Worksheet
        sPartNumber = InputBox("Please enter the Part Number:")
        If sPartNumber = "" Then Exit Sub
        Set rData = ThisWorkbook.Worksheets("Work Data").Columns("C")
        Set rFind = rData.Find(sPartNumber)
        If rFind Is Nothing Then
            MsgBox "There were no matches.", vbInformation
            sFirstMatch = rFind.Address
                If rRows Is Nothing Then
                    Set rRows = rFind
                    Set rRows = Union(rRows, rFind)
                End If
                Set rFind = rData.FindNext(rFind)
            Loop While rFind.Address <> sFirstMatch
            On Error Resume Next
            Set ws = ThisWorkbook.Worksheets(csQuery)
            On Error GoTo 0
            If ws Is Nothing Then
                Set ws = ThisWorkbook.Sheets.Add
                ws.Name = csQuery
            End If
            ThisWorkbook.Worksheets("Work Data").Rows(1).Copy ws.Rows(1)
            ws.Rows(2).PasteSpecial xlPasteValues
            ws.Cells.PasteSpecial xlPasteColumnWidths
        End If
    End Sub

    Felipe Costa Gualberto -

    Monday, March 18, 2013 11:50 PM
  • Try the link below and download the file.

    To see the VBA code right click Sheet1 tab name and select view code.  You will need to edit the paths of the 2 lines between the asterisk lines.

    The workbook with the code is stand alone. Click the button to open the master file and calculation files. You will then see a message to select the number required from the ComboBox and the data will be extracted and displayed in Calculation.xlsx.

    See the optional code that is commented out towards the bottom of the code. Comments start with <---***.   You might want to use this code to close the workbooks no longer required and just leave calculation.xlsx.

    Also, in the VBA editor double click ThisWorkbook in the Project Explorer and see the Workbook_Open event. You can remove the leading single quote to uncomment the code that calls CommandButton1_Click and it will run automatically when the workbook is opened.

    OK the upload did not work because the VBA code gets stripped so trying a new link with a zipped file!186&authkey=!AG1LgX2VsD1s6UE

    Regards, OssieMac

    • Edited by OssieMac Tuesday, March 19, 2013 6:29 AM
    Tuesday, March 19, 2013 6:20 AM
  • Please ignore the initial link that I posted and use the new link. (I have edited my previous post and placed a new link to a zipped file.)

    Just as well that I had an after thought and decided to test after I had posted because the VBA code was stripped out of the excel file when I downloaded it.

    Previously (some time ago) when I tried to upload a file with VBA code it refused the upload and this time it accepted the upload with no alerts. Based on info from Oskar Shon, I assumed that all was good but when I attempted to download it again the VBA code was missing.

    I have tested downloading the zipped file and it appears to be good so look forward to hearing how you go with it.

    Regards, OssieMac

    Tuesday, March 19, 2013 6:47 AM
  • Benzadeus and OssieMac:

    You both supplied code that executes what I was asking for, and with two different approaches which is great! This is how I learn how to properly structure VBA (trust me, if you were to see how I make two ends meet in VBA right now you'd laugh your End Subs off... I am not joking :p ).

    Anyways, I need to go into break/fix mode to determine how to adapt you all's code into my files. 

    Benzadeus, your code worked as advertized, but I need to work that code into a separate workbook so the "database" has nothing attached to it at all (no functions, no macros... just rows and columns of searchable data). It's just a syntax challenge on my part. Your code worked very well!

    OssieMac, your code also worked as requested. The only thing I need to try and figure out is how to pull data from the "Master" and immediately close it. To avoid the "file is already open/read only/etc" issue with regular files I only want it to be actually open for a moment while data is pulled/calculated. Your approach is very different from how (in my lack of prowess) I would have tried to structure it, but I like it very much.

    So, guys is you don't mind give me a couple days to try and break/fix the code you've given me so I can learn.More than likely I'll have more questions, but as they say... Give a man a fish and he'll eat for a day... teach a man to fish and he'll never go hungry.

    I want to learn how to fish! :) Thanks again, guys!

    Wednesday, March 20, 2013 3:03 AM
  • The only thing I need to try and figure out is how to pull data from the "Master" and immediately close it.

    There were instructions in my previous post for you to uncomment code to do this. I have uploaded another zipped workbook that runs the preparation code immediately the workbook opens and requests the user to select the required number.

    I have removed the button to run the preparation code because it is not needed if the code executes immediately upon opening.

    Immediately the required number is selected, the code executes and copies the data to Calculate and then closes the Master and the workbook containing the code and leaves the user with Calculate workbook. Let me know if you require any more changes.

    The first time you open the amended workbook it will error on the path to your workbooks. End the code and then edit the paths between the asterisk lines and save the workbook and close and re-open.

    New link:!187&authkey=!AHZXZIDsdUzZjxM

    Regards, OssieMac

    Wednesday, March 20, 2013 3:54 AM
  • @smaier69

    As the download link to your example workbook expired, I can't get to test a final version. But if the only requirement missing is to display the data in another workbook, just a few adaptions will do: just create a new standalone and refer ws to the first Worksheet of that workbook and put the data there.

    Felipe Costa Gualberto -

    Wednesday, March 20, 2013 11:11 PM