Hi guys
Please could someone help with this dilemma?
I have the below vba which works fine but I now need a another search to search and only report back those sheets that conform.
At the moment it searches all sheets to find a text and returns all results. Is it possible to return only results where a cell on that row does not have a date in it?
ie: Finds DSO15008 in sheet1, sheet2, sheet3 but only returns sheet1 and sheet3 because the DSO15008 on sheet2 has a date in column I (ie:job is finished)?
Please could someone help with this dilemma?
I have the below vba which works fine but I now need a another search to search and only report back those sheets that conform.
At the moment it searches all sheets to find a text and returns all results. Is it possible to return only results where a cell on that row does not have a date in it?
ie: Finds DSO15008 in sheet1, sheet2, sheet3 but only returns sheet1 and sheet3 because the DSO15008 on sheet2 has a date in column I (ie:job is finished)?
VBA Code:
Public Sub FindText()
Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter text to find")
If myText = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
With ws
'Do not search sheet4!
If ws.Name = "Input" Then GoTo myNext
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
Set Found = .UsedRange.FindNext(Found)
'Copy found data row to sheet4 Option!
'Found.EntireRow.Copy _
'Destination:=Worksheets("Dashboard").Range("C34").End(xlUp).Offset(1, 0)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
myNext:
End With
Next ws
If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If
End Sub