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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Really deserves an A+. Thank you again!
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.
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.C
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
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.
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.C
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
ASKER
This makes sense and works perfectly. Thank you very much for making this difficult task much more palatable.