Macro to loop through workbooks and find certain text in multiple rows

[rachel06]

Hi!

Not sure if this is possible, but I'm hoping to get some direction to get started if it is!

I have to review several Excel spreadsheets that have different contract language. I'm looking for a specific "blurb" in the spreadsheets, and there is a finite number of "blurbs", ranging from 8 to 17 rows. I currently have all of the existing "blurbs" on a sheet I'm calling "Lookup." These are just in row 1 and down, and each one is in a separate column.

The workbook also has a sheet called "Output" where I'd like the magic to happen.

What I'm looking for the macro to do is this:

1. Place the file name in Column A
2. Put a number in Column B based on what language is in the worksheet

On the Lookup sheet, I have converted each "blurb" to a named range, thinking that might make this easier. They're named Range1 - Range7. I'm hoping there's a way to say like if Range1 is found, that row in Col B (next to the file name in Col A) would be 1, if Range2 is found that row in Col B would be 2 etc. If there isn't a match, I'd want that cell in B2 to be blank. I'd also want a way to be able to add more ranges if needed, which I'm sure would be easy enough.

Hope this all makes sense.

TIA!
Rachel

 

[Micron]

I'm sure it's all doable - not that I 100% understand your description or know the missing parts. What I've bolded are key words that I'd search on if googling this.

I have to review several Excel spreadsheets that have different contract language.

They're all in the same wb or no? Not hard to loop over a set of wb's and/or loop over a set of Excel sheets therein. Lot's of code examples posted everywhere on how to do this.

there is a finite number of "blurbs", ranging from 8 to 17 rows

That means there are 9 rows of "blurbs" (a blurb is a string to look for?) or they are in rows 8 to 17? Again, not hard to loop over an Excel range if that's the need.

These are just in row 1 and down, and each one is in a separate column.

That statement makes it harder to understand what they are. Wondering why the blurbs are not in one column (easier), or one row.
If each of those were in a column, no need for named ranges. That's just more design work compared to using cells in one column. To add or edit, all you'd have to do is edit or append in the column. If you've created a named range for a blurb, why would it not "be found"? You mean it might not have a value?

2. Put a number in Column B based on what language is in the worksheet

So you have associate numbers with languages, like 1 = English; 2=German; 3=French and so on?

Aside from answering a bunch of questions, it might help to post a mocked up range of cells that explain things a bit better. Or start googling the bold text and see where that leads you.

 

[rachel06]

Hi @Micron, appreciate the response!

I was assuming this was easy to do, but whatever I was Googling was bringing up something complete different so I was kind of spinning my wheels. I think you've given me a great place to start though and better phrases to Google, and I so appreciate that!

To answer this one (and add one small question):

"These are just in row 1 and down, and each one is in a separate column."

I just have each blurb running across the spread sheet in row 1. So like one blurb might be in A1-A7 then the next one is in B1-B12, then C1-C17, etc, and I've converted these all to ranges named like Range1, Range2, Range3. Are you saying it'd be easier to have them more like A1-A12, A14-A25, A27-A43, etc?

And also the blurb might not be found as sometimes new ones are added that I might not have included in my workbook yet. Unfortunately, another team manages those and we don't know there are new ones until they get to us.

Thanks again for giving me great wording on what to search for though! I'm going to start there!

 

[Micron]

I'm saying in a column but the same column is how I would do it, but maybe not if I knew what you knew about it all. That was based on them being in one cell. You seem to be saying you have these spread across columns. How can one 'thing' be spread across columns? You merged A1:A7? Maybe you need to show some data for clarification on that part. It would seem to me that if, for example, each blurb (still don't know what that is) went down a column you wouldn't need named ranges and there'd be nothing you'd need to do in that respect when you get new info. You'd just need to add new ones to your column. Maybe you should explain how you get the data as well. It may be that whatever code you end up with should go into your personal.xls and you just work with the wb they send you, assuming that's what you get.

 

[rachel06]

I'm saying in a column but the same column is how I would do it, but maybe not if I knew what you knew about it all. That was based on them being in one cell. You seem to be saying you have these spread across columns. How can one 'thing' be spread across columns? You merged A1:A7? Maybe you need to show some data for clarification on that part. It would seem to me that if, for example, each blurb (still don't know what that is) went down a column you wouldn't need named ranges and there'd be nothing you'd need to do in that respect when you get new info. You'd just need to add new ones to your column. Maybe you should explain how you get the data as well. It may be that whatever code you end up with should go into your personal.xls and you just work with the wb they send you, assuming that's what you get.

Got it. I think you have me pointed where I need to go now. I completely understand why having them all in one column makes sense now.

Also, these "blurbs" are just a section of the workbook that gives some exceptions to claims that we consider to be eligible, and these vary by client. They span across 8-17 or so rows in each spreadsheet (so some might be 8 rows, some might be 16, some might be 9, etc) but the first row in all of them is exactly the same, which is how we know where they start. Right now, I've found 7 different ones and I had one in column A, one in Column B, etc. I didn't merge the cells as they aren't merged in the workbooks I receive.

Anyway, thanks for the feedback! I think I know where to go from here!!

 

[Micron]

OK, so each blurb comprises a different number of rows (say 7) and somehow you've got 7 individual parts/lines of that blurb spread over that many rows and yet the cells are not merged. So you'd be looking for a particular sentence/phrase (because the first "line" is always the same). Then you want to get the rest of that blurb (or at least know where it ends)? That is going to be the tricky part, I think. If you need to know that, something will have to define the end such as a blank cell or row, or the same beginning text denotes the end of one and the beginning of another. At first I didn't understand that so I am beginning to think that named ranges may be the way to go after all. That's because I think you said you've defined these 7 strings (using 7 rows as an example) to one range (e.g. A1:A7) and you're doing a lookup with the contents of that range. That is like having the entire blurb in one cell, which is what I originally meant. Sorry for any confusion I might be causing because of not being able to see any data content or layout. Good luck!

 

[rachel06]

Yeah that's exactly why I thought I'd need named ranges! I do have a blank line after each one as well. No worries about any confusion! Your suggestion about putting them in one column makes sense (reading top to bottom instead of left to right). My brain went to IF it doesn't match column 1, check column 2, then check column 3, etc until a match is found (and skip if a match isn't found) which is way too complicated!