Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-21
7
Medium Priority
?
288 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 53

Expert Comment

by:Rgonzo1971
ID: 39796645
Hi,

Is your filename pointing to the right file?

is the strreference actually valid?

Regards
0
 
LVL 53

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 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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

Technology Partners: 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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

824 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