I am running the following macro to search for Account #'s in other excel
files. It is working great, except some of the files have worksheets with
Text Boxes on them, and the account # has been entered into the text box -
and the macro won't find it there. Is there any change that can be made to
also search within the text boxes?
Thanks!
Ann
Macro (Ignore the >> from my cut/paste):
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:=xlPart _
>> , 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
Bookmarks