Search/Compare VBA Help

[Ann]

I posted this problem yesterday and got great help from Rowan, but I am
having a problem and the original question is now on Page 4 so I am reposting
it. I'd appreciate anyone who could tweak the VBA to help. (see below).
Thanks!

Original Problem:
Rowan's VBA answer:

See a similar request here: http://tinyurl.com/9lj44

Try this amendment:

Sub AcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim AcNo As String
Dim eAc As Long
Dim i As Long
Dim sh As Long
Dim fndAc As Range

On Error GoTo Errorhandler
Application.ScreenUpdating = False

eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Data") 'change directory

For i = 2 To eAc
AcNo = Sheets("Sheet1").Cells(i, 1).Value

For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name

With Workbooks(objFile.Name)
For sh = 1 To .Sheets.Count
With .Sheets(sh).Cells
Set fndAc = .Find(AcNo _
, lookat:=xlWhole _
, MatchCase:=True)
End With
If Not fndAc Is Nothing Then
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 3).Value = "Yes"
Exit For
End If
Next sh
.Close False
End With
Set objFile = Nothing
End If
Next
With Sheets("Sheet1").Cells(i, 3)
If .Value <> "Yes" Then .Value = "No"
End With
Next i

Errorhandler:
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

Hope this helps
Rowan

New Problem:

I set up a test file with 6 Accouint #s in column A and descriptions in
column B. And a folder with one Excel workbook (multiple sheets) in C:\Test.
The first 3 account #'s are in the workbook, the last 3 are not.

I am getting "No" for all 6, because the Account numbers in the test file
are not always the only data in the cell. For example: the cell actually
contains "Account 03-32467", when the test file only specifies "03-32467". If
I change the entry in the workbook to show only "03-32467", I do get a "Yes"
returned, so the program you gave me is working - but it is not searching
within each cell.

Unfotunately, the workbooks I will be searching will have the account #'s in
cells with other text. Is there a way to modify the program to search
specifically for the account # within other text. It will not always be alone
in a cell.

Thanks!
Ann

 

[Bernie Deitrick]

Ann,

Change

Set fndAc = .Find(AcNo _
, lookat:=xlWhole _
, MatchCase:=True)

to

Set fndAc = .Find(AcNo _
, lookat:=xlPart _
, MatchCase:=True)

HTH,
Bernie
MS Excel MVP

 

[Mike Q.]

At a quick glance I think you need to change
Set fndAc = .Find(AcNo _

, lookat:=xlWhole _
, MatchCase:=True)

to
Set fndAc = .Find(AcNo _

, lookat:=xlPart _
, MatchCase:=True)

Hope this helps. I did not test it.

 

[Ann]

Thank you Bernie and Mike! That was the problem!

Ann,

Change

Set fndAc = .Find(AcNo _
, lookat:=xlWhole _
, MatchCase:=True)

to

Set fndAc = .Find(AcNo _
, lookat:=xlPart _
, MatchCase:=True)

HTH,
Bernie
MS Excel MVP