VBA to search for multiple items in workbook

[somesoldiers]

Hi Guys

I have a list of 200 unique strings, I need to find which of these is not contained in a workbook with multiple tabs, can anyone advise on a quick way to do this please?

thanks

Noel

 

[HalfAce]

Hi Noel,
A couple questions for you.
I have a routine I use regularly to search through the entire workbook that shows me where each instance of my search term can be found.

1) Do you want to know if/where any of these terms are found, or do you only want to know which ones can NOT be found?

2) Where is your list of 200 strings located?

3) How do you want it to inform you of what it finds? (message box?, listed on a sheet somewhere? ...?)

 

[somesoldiers]

Hi HalfAce

just to highlight which ones aren't on the workbook would be great.

can locate within workbook on seperate tab if that makes it easier

doesn't have to be too pretty maybe turn the missing ones red or mark with N/A even would be great

thanks

Noel

 

[HalfAce]

Do you have a location for the list to search through so we can loop through it and turn them (for example) red if they're not found?

 

[somesoldiers]

If I just stick both on C drive for now one called "List" one "Workbook to search" maybe and I can adjust the code to suit my system- would that work?

 

[HalfAce]

Well, what I meant was, when you said -

I have a list of 200 unique strings, I need to find which of these is not contained in a workbook...

I'm assuming you have a range (of 200 cells?) somewhere within an excel workbook and you want to check each value of that range to see if it's contained within that (or another?) workbook. (right?)
If you were to open the workbook containing the list of 200 strings, where would we find that range in the workbook? - Also, if this is to search a different workbook, what is the name of that workbook? - And (also again), how many workbooks is this supposed to search through?

If it's meant to open up and search through different workbooks then yes, we'll need to know the file path(s) of the workbook(s) to open & search, but if it's just searching through the same workbook that contains the 200 strings, then we'll only need to know where in the workbook that list of strings resides. (In either case, we'll need to specify each cell in that range to know which string we're searching for, so clearly we'll need to know where that range is.)

(See what I mean?)

 

[somesoldiers]

hey, strings may not have been the right word, sorry
example of cells that that I will be looking to search the wb for

<TABLE style="WIDTH: 71pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=95><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; WIDTH: 71pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28588 height=17 width=95>B500XTC-01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28588 height=17>B500XT9-01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ccffcc; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl28588 height=17>B500VYV-01</TD></TR></TBODY></TABLE>

all in one workbook wb name Cash Rec. All instances of above should be searched for on WASH tab, Column N. I only need to know if the above aren't on the WASH tab.
Let me know if you need anything else

thanks for your help on this and sorry again for being unclear

noel

 

[HalfAce]

OK, yeah that makes it much easier than what I had imagined.
Though, I still don't know the range of your 200 strings that you want to go through so I wrote up a little example using a couple of assumptions.

This will go down from A1 to A200 (of the active sheet), checking to see if the value of each cell can be found in column N of the sheet named "WASH".
Since I also don't know what exactly you want to do when the search condition is found, I just had it list in column B, whether or not the value in column A was found. (and where it was found if it was.)

It should be a good place to start and after you check it out you can come back with any changes you want to make.

Try this for now and see where it gets us.

Sub MissingValuesDemo()
On Error GoTo 0
Dim SearchRng As Range, SearchVal As Range, FoundVal As Range

'''///(This is the range we need to know the real location of _
       if we want to search through it.)
Set SearchRng = Range("A1:A200") 'Replace (A1:A200) with the list's real range

For Each SearchVal In SearchRng
  Set FoundVal = Sheets("WASH").Columns("N:N").Find(What:=SearchVal, lookat:=xlContents)
  If FoundVal Is Nothing Then
    SearchVal(, 2).Value = SearchVal & " was not found."
  Else
    SearchVal(, 2).Value = "Found " & SearchVal & " in cell " & FoundVal.Address(0, 0)
  End If
Next SearchVal
End Sub

Hope it helps. (Let us know if it doesn't...)

 

[somesoldiers]

Thanks Halface, tha works perfect, sorry for the late reply. Just one thing, would it be possible to add a second sheet to search named "UBS" at the same time, if it's a lot of effort don't worry will just run a second macro and compare both sets of results

thanks

Noel

 

[somesoldiers]

or even better , if it could search the whole workbook and advise if each value is not on any other tab than the one containing the search list?