Solved

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

Posted on 2014-01-21
7
257 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now