Solved

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

Posted on 2014-01-21
7
273 Views
Last Modified: 2014-01-22
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
0
Comment
Question by:Jagwarman
  • 3
  • 3
7 Comments
 
LVL 3

Expert Comment

by:Sreeram
ID: 39796618
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
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39796645
Hi,

Is your filename pointing to the right file?

is the strreference actually valid?

Regards
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39796710
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jagwarman
ID: 39796889
Rgonzo1971 that worked. What did you do?
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39796960
Hi,

I added

If Not (FoundRef Is Nothing) Then
            If FoundRef <> "" Or bFound Then bFound = True
        End If

Open in new window

and replaced

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

Open in new window

with

If bFound = False Then
   ThisWorkbook.Sheets("Sheet1").Range("J6") = "No"
Else
   ThisWorkbook.Sheets("Sheet1").Range("J6") = "Yes"
End If

Open in new window


SO first I test if the search in the current sheet doesn't create an oject
then if the search is not empty ("") or already successful, bFound is true

I then use bFound to select Yes or No

Regards
0
 

Author Closing Comment

by:Jagwarman
ID: 39797228
brilliant thanks
0
 

Author Comment

by:Jagwarman
ID: 39800335
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question