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,
On the last two posts... yeah we can make it do that.

I have a handful of meetings this morning and a couple this afternoon so it ain't lookin' good for today but if you're not in too big a hurry I'll take a look at it in a day or so. I know I already use a routine that will search through the entire workbook so that particular wheel already exists, we'll just need to beat it a little rounder is all.

 

[somesoldiers]

no worries at all, was just thinking to myself when I ran "why didn't I just do a vlookup and stop bothering the good folk at the forum" but yeah to search the whole wb would be the ultimate goal.

appreciate all the help you have given so far and appreciate any time you can spare whenever.

Noel

 

[HalfAce]

"why didn't I just do a vlookup and stop bothering the good folk at the forum"

No bother at all man. It's what we do.

 

[somesoldiers]

Hey HalfAce- any more thoughts on this one? thanks Noel

 

[HalfAce]

Oh, I thought you'd settled on using vlookup...

For vba, how about something like this?

Sub SearchSheetsWASHAndUBS()
Dim LstRw As Long, SrchRng As Range, SrchTrm As Range

'''Amend the next line to the real range where your values to search for reside.
Set SrchRng = Range("A1:A200")

For Each SrchTrm In SrchRng

  With Sheets("WASH")
    LstRw = .Cells(Rows.Count, "N").End(xlUp).Row
    On Error Resume Next
    Set Found = .Range("N1:N" & LstRw).Find(SrchTrm, lookat:=xlContents)
    If Not Found Is Nothing Then
      MsgTxt = SrchTrm & "  is located on '" & .Name & "' sheet in cell " & Found.Address(0, 0)
      MsgBox MsgTxt
      SrchTrm.Font.ColorIndex = 3
    End If
    On Error GoTo 0
  End With
  
  With Sheets("UBS")
    LstRw = .Cells(Rows.Count, "N").End(xlUp).Row
    On Error Resume Next
    Set Found = .Range("N1:N" & LstRw).Find(SrchTrm, lookat:=xlContents)
    If Not Found Is Nothing Then
      MsgTxt = SrchTrm & "  is located on '" & .Name & "' sheet in cell " & Found.Address(0, 0)
      MsgBox MsgTxt
      SrchTrm.Font.ColorIndex = 3
    End If
    On Error GoTo 0
  End With
  
Next SrchTrm

End Sub

I don't know where your list of (200 or so) search terms are located so this assumes it is in range A1:A200 of the active sheet.

Does that get you any closer?

 

[somesoldiers]

yep that is perfect, thanks a lot

FYI have omitted the Msg Box for now but like the way this works and will be sure to hold on to it for future reference

thanks again

 

[HalfAce]

Yeah, I put those in basically just for testing. Only realized after posting it that I forgot to remove them.

Anyway, glad it helped.