• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

ID and extract text in Excel

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.
LookupSample.xlsx
0
behest
Asked:
behest
  • 2
  • 2
1 Solution
 
Jaes OverleyConsultantCommented:
behest:

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)
'http://www.experts-exchange.com/Software/Microsoft_Applications/Q_28474578.html
    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,

Jaes
0
 
behestAuthor Commented:
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.
0
 
behestAuthor Commented:
Really deserves an A+. Thank you again!
0
 
Jaes OverleyConsultantCommented:
behest:

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,

Jaes
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now