Solved

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

Posted on 2014-01-21
7
264 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 49

Expert Comment

by:Rgonzo1971
ID: 39796645
Hi,

Is your filename pointing to the right file?

is the strreference actually valid?

Regards
0
 
LVL 49

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

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

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

777 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