asked on

Follow up on question "search for text in a file in a folder from an open file"

I have been using this code provided by Rgonzo1971 which has been working fine, until last week. Now it no longer returns Yes only No
when I check the file manually and do a 'Find' I can see the item I am looking for. When I run the macro it puts "No" in J6

Would you have any idea what might be causing this

I have done all the usual checks, looking for spaces, checking the format etc.

Sub IsReferenceThere()
Application.ScreenUpdating = False
strReference = "aaaaaaaaa" ' Change here your reference

Set SrcWbk = Workbooks.Open(Filename:="G:\Clients\Open Invoice\Open Invoice Documentation Database - UPDATED.xlsx")
For Each sh In SrcWbk.Sheets
With sh
Set FoundRef = .Cells.Find(What:=strReference, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
End With
Next
If FoundRef Is Nothing Then
ThisWorkbook.Sheets("Sheet1").Range("J6") = "No"
Else
ThisWorkbook.Sheets("Sheet1").Range("J6") = "Yes"
End If

Application.DisplayAlerts = False
SrcWbk.Close
Application.DisplayAlerts = True

End Sub

Sreeram

Try this :

Sub IsReferenceThere()
Application.ScreenUpdating = False
strReference = "aaaaaaaaa" ' Change here your reference

Set SrcWbk = Workbooks.Open(Filename:="G:\Clients\Open Invoice\Open Invoice Documentation Database - UPDATED.xlsx")
For Each sh In SrcWbk.Sheets
With sh
Set FoundRef = .Cells.Find(What:=strReference, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
End With
Next
'Add this line of code and check
DoEvents

If FoundRef Is Nothing Then
ThisWorkbook.Sheets("Sheet1").Range("J6") = "No"
Else
ThisWorkbook.Sheets("Sheet1").Range("J6") = "Yes"
End If

Application.DisplayAlerts = False
SrcWbk.Close
Application.DisplayAlerts = True

End Sub

Rgonzo1971

Hi,

Is your filename pointing to the right file?

is the strreference actually valid?

Regards

Rgonzo1971

Hi,
pls try

Sub IsReferenceThere()
Application.ScreenUpdating = False
strReference = "aaaaaaaaa"     ' Change here your reference

Set SrcWbk = Workbooks.Open(Filename:="G:\Clients\Open Invoice\Open Invoice Documentation Database - UPDATED.xlsx")
bFound = False
For Each sh In ThisWorkbook.Sheets
    With sh
        Set FoundRef = .Cells.Find(What:=strReference, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
    End With
        If Not (FoundRef Is Nothing) Then
            If FoundRef <> "" Or bFound Then bFound = True
        End If
Next
If bFound = False Then
   ThisWorkbook.Sheets("Sheet1").Range("J6") = "No"
Else
   ThisWorkbook.Sheets("Sheet1").Range("J6") = "Yes"
End If
Application.DisplayAlerts = False
SrcWbk.Close
Application.DisplayAlerts = True

End Sub

Open in new window

Regards

Jagwarman

ASKER

Rgonzo1971 that worked. What did you do?

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagwarman

ASKER

brilliant thanks

Jagwarman

ASKER

Rgonzo1971, sorry about this. It did find the item I was looking for yesterday and that was fine. What I did not test was to get it to look for an item that was not on the list. Did that today and it said yes even though the item is not on the list.

Follow up on question &quot;search for text in a file in a folder from an open file&quot;

Follow up on question "search for text in a file in a folder from an open file"