Need More Help for Searching Textboxes

[Guest]

I've gotten great help from Tom Ogilvy and K Dales on this already, but I
can't get it to run correctly and really need to get moving on this project.

My current code is listed below. I have an Excel file with 2000+ account
numbers that I am searching for in a folder with Excel workbooks. I get back
a "Yes" if it is found and a "No" if it's not.

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.

Tom suggested that I add this code to the original, but I can't get it into
the correct place to run:

Dim tbox As Textbox

For Each tbox In .sheets(sh).Textboxes
If instr(1,AcNo,tbox.Text,vbTextcompare) Then
' AcNo found

End If
Next


Any help will be greatly appreciated. I have many, many files to search and
unfortunately, there is a bunch of the with the Textboxes.

Original code (runs great, except it misses the textboxes):

Sub FastAcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim tbox As TextBox
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:\Documents and Settings" & _
"\zzfy98\My Documents\Test") 'change directory

For Each objFile In objFolder.Files

If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name, UpdateLinks:=False

With Workbooks(objFile.Name)
For sh = 1 To .Sheets.Count
bDone = True

For i = 1 To eAc
If LCase(ThisWorkbook.Sheets("Sheet1") _
.Cells(i, 2).Value) <> "yes" Then
' All accounts not found

bDone = False
AcNo = ThisWorkbook.Sheets("Sheet1").Cells(i,
1).Value

With .Sheets(sh).Cells
Set fndAc = .Find(AcNo _
, LookIn:=xlValues _
, Lookat:=xlPart _
, MatchCase:=True)

End With

If Not fndAc Is Nothing Then
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 2).Value = "Yes"
End If
End If
Next i

If bDone Then
.Close False

Exit Sub
End If
Next sh
.Close False
Set objFile = Nothing
End With
End If
Next

For i = 1 To eAc

With ThisWorkbook.Sheets("sheet1")
If IsEmpty(.Cells(i, 2)) Then
.Cells(i, 2).Value = "No"
End If
End With
Next

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

End Sub

 

[Guest]

Trying to interpret your code:
I see where you search for an Excel file and open it, then you are stepping
through each sheet in the book. Then, on each sheet, you step through rows 1
to eAc in your original workbook (assume your account no list). You look
for a 'yes' in column B; apparently the flag that the account was found in
another book. If not, you look through the sheet in the book you opened
earlier. You check the entire sheet's .Cells for the account number. Then
you check the result as below:
If Not fndAc Is Nothing Then
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 2).Value = "Yes"
End If
Now, if I am following your logic correctly, it seems the best place to look
in the textboxes is here; i.e. if there are no cells on this sheet that
contain the acct no, look in the textboxes. This would occur when fndAc is
Nothing, so change the above into an IF... THEN... ELSE statement:

If fndAc Is Nothing Then
' ... use Tom's code here to search the textboxes
For Each tbox In .sheets(sh).Textboxes
If instr(1,AcNo,tbox.Text,vbTextcompare) Then 'Acct no found it
textbox:
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 2).Value = "Yes"
End If
Next
Else ' this is your original code; means acct no found in cells:
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 2).Value = "Yes"
End If

 

[Guest]

Thank you, but it is not working. It opens the file, then stops - leaving the
file open and it has not returned any results (either "Yes" or "No").


I pasted your code in where you directed and the resulting code (that is
stopping) is below. Have I missed something?

Thanks!
Ann

Sub FastAcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim tbox As textbox
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:\Documents and Settings" & _
"\zzfy98\My Documents\Test") 'change directory

For Each objFile In objFolder.Files

If objFile.Type = "Microsoft Excel Worksheet" Then

Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name, UpdateLinks:=False

With Workbooks(objFile.Name)

For sh = 1 To .Sheets.Count

bDone = True

For i = 1 To eAc
If LCase(ThisWorkbook.Sheets("Sheet1") _
.Cells(i, 2).Value) <> "yes" Then

' All accounts not found

bDone = False

AcNo = ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value

With .Sheets(sh).Cells

Set fndAc = .Find(AcNo _
, LookIn:=xlValues _
, Lookat:=xlPart _
, MatchCase:=True)


End With

If fndAc Is Nothing Then
' ... use Tom's code here to search the textboxes
For Each tbox In .Sheets(sh).TextBoxes
If InStr(1, AcNo, tbox.Text, vbTextCompare) Then 'Acct no found
it textbox:
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 2).Value = "Yes"
End If
Next
Else ' this is your original code; means acct no found in cells:
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 2).Value = "Yes"
End If

End If

Next i

If bDone Then
.Close False
Exit Sub
End If
Next sh
.Close False
Set objFile = Nothing
End With
End If
Next

For i = 1 To eAc
With ThisWorkbook.Sheets("sheet1")
If IsEmpty(.Cells(i, 2)) Then
.Cells(i, 2).Value = "No"
End If

End With

Next

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

End Sub

 

[Guest]

Does it stop due to an error, or just stops early? I would guess that either
it is running into a VBA error or else it is exiting one of your loops before
it is really done. It may be hard to tell which one of these things is
occurring due to your error handler; if there is an error it will exit
"gracefully" but not give you an error message. I would suggest disabling
the error handler while you debug it.

It is hard to debug the code "by eye" without running it and I have limited
time but will take another look and see if I can find what is going wrong.

 

[Guest]

That was my thought - that it was dropping out of a loop. It is not giving me
an error, just stopping and leaving the file open, so I know it isn't getting
all the way to the end.

I'm leaving for the day, but will come back in the morning and try taking
out the errorhandling & work some more.

Thanks for your time,
Ann