ID and extract text in Excel

Posted on 2014-07-12
Last Modified: 2014-07-13
My goal is to evaluate a column of data to 1) determine if a pre-defined name is present and 2) enter that pre-defined name into a column on the same row. There are 15 pre-defined names and the possibility of a thousand or so rows to filter through. Only one name is present per row.

I tried a looping vlookup VBA code that defined one name parameter to find. This worked but when I duplicated the code and changed the name parameter (new macros) it stops working after the 6th or 7th macro.

Is there a way to have Excel compare the names in one table with the contents of the cells in a row in another table and, if one of the names is found, return that name in an offset cell in the second table?

Attached is a sample workbook with more details on what I am trying to achieve.
Question by:behest
    LVL 1

    Accepted Solution


    Greetings! As with all Excel problems there are multiple solutions, but this one works for your test workbook.

    Sub FindNamesWithinComments()
    '2014.07.12 - Jaes W Overley (OpenWaterSwim)
        Dim wsC As Worksheet, wsD As Worksheet
        Dim rngC As Range, rngD As Range, rngCL As Range, rngDL As Range
    'Turn off Calculation and ScreenUpdating to run faster and clear StatusBar
        With Application
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .StatusBar = False
        End With
    'Set the worksheet object variables
        Set wsC = ThisWorkbook.Sheets("Comments")
        Set wsD = ThisWorkbook.Sheets("Details")
    'Set the Comment Range to look in
        With wsC
            Set rngC = .Range(.[B2], .[B16])
        End With
    'Set the Detail Range to look from
        With wsD
            Set rngD = .Range(.[A2], .Cells(.Cells(.Cells.Rows.Count, "A").End(xlUp).Row, "A"))
        End With
    'Loop through the Comment range
        For Each rngCL In rngC
    'Loop through the Details range
            For Each rngDL In rngD
    'Check for a match, if matches update statusbar with Completion progress and exit to search next comment
                If InStr(rngCL.Value, rngDL.Value) Then
                    Application.StatusBar = "Complete: " & Format(rngD.Count / rngD.Row, "0%")
                    wsC.Cells(rngCL.Row, "F").Value = rngDL.Value
                    Exit For
                End If
            Next rngDL
        Next rngCL
    'Turn on Calculation and ScreenUpdating and clear StatusBar
        With Application
            .Calculation = xlAutomatic
            .ScreenUpdating = True
            .StatusBar = False
        End With
    'Clear object variables to free computer memory
        Set wsC = Nothing
        Set wsD = Nothing
        Set rngC = Nothing
        Set rngD = Nothing
        Set rngCL = Nothing
        Set rngDL = Nothing
    End Sub

    I added comments to my code, but please let me know if there is any part of the code you do not understand.

    Kindest Regards,


    Author Comment

    Well, gosh. Those range things are pretty nifty. I sure am glad I stopped beating my head against the wall of looping vlookups.

    This makes sense and works perfectly. Thank you very much for making this difficult task much more palatable.

    Author Closing Comment

    Really deserves an A+. Thank you again!
    LVL 1

    Expert Comment

    by:Jaes Overley

    You are quite welcome. Excel VBA is an Object Oriented Programming (OOP) language, which means that you can work with all the parts as specific Objects. In this program, I created some Worksheet objects to reference the different Worksheets and some Range objects to reference the Range of Comments and the Range of Names. You can loop through Range objects one at a time by using the For Each loop statement. Since you were looking for a specific name within a cell, I looped through each of the comments and within that loop I loop through each of the name values you have to search. Once I find a Name within a Comment, I put that Name value in the Child Name column. Since we assume only one child will be in each comment, I exit that inner loop to make the program run even faster.

    I have a couple things I wanted to say now that I go back and look through the program again. First, if your Comments are in a sheet without the examples you could use the same method I used to find the last cell in the range by changing this line.

    Set rngC = .Range(.[B2], .[B16])

    to this

    Set rngC = .Range(.[B2], .Cells(.Cells(.Cells.Rows.Count, "B").End(xlUp).Row, "B"))

    Additionally, the following example is a good way to assign the last comment row provided there was nothing beneath it.

    Dim lngRow as Long
    lngRow = wsC.Cells(wsC.Cells.Rows.Count, "B").End(xlUp).Row

    I also noticed that I made one error in the above code. The StatusBar Completion update is incorrect. Please replace that line with the following line.

    Application.StatusBar = "Complete: " & Format(rngCL.Row / rngC.Count, "0%")

    The way that this works is that you divide the current row by the total number of rows and then format it as a percent. I learned all I know from Experts-Exchange and if you have an interest you can learn plenty here. Glad that my solution worked for you. Please let me know if you have any other questions.

    Kindest Regards,


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Third Sunday of the Month 10 48
    Using Pop Up Calculator in Excel 4 27
    Protecting an object 3 23
    Windows 10 Modified 2 10
    PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now