VBA- search within a search to return a specific selection.

pete4monc

Board Regular
Joined
Jan 12, 2014
Messages
53
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)?

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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
I think we need a little clarification on your date.
When we find the value you are looking for, are we always checking column I for the date?
If not, please explain this date logic in more detail.
 

pete4monc

Board Regular
Joined
Jan 12, 2014
Messages
53
Basically the date will always be entered in column I only when that job is finished. So the vba i have searches through multiple worksheets (departments) and returns all sheet names where that order number features. But I wish it only to return those sheet names that the date in column I have not finished.
Hope that makes sense?

Attached clip below for you to see. It returns all sheets that have that order number, but Sales spreadsheet has completed their part so I don't want it to report any sheets that have been completed.

Thanks in advance.

dash.PNG
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
In Excel dates are stored as numbers (and then just have a special date format applied to it).
So all you need to do in your code is before you add the address to your list and increment your counter, first check to see if column I is greater than 0.

So you will need to do something something like this within your Do Loop:
VBA Code:
If ws.Cells(Found.Row, "I") > 0 Then
    'your getting address and incrementing counter code here
End If
 

pete4monc

Board Regular
Joined
Jan 12, 2014
Messages
53

ADVERTISEMENT

Hi @Joe4

I have added the code to my original code but now it comes up with the message no order number found? But I know it's there?

dash2.PNG


Maybe I have miss understood where I need to put your code?

VBA Code:
Public Sub FindText()
'Run from standard module, like: Module1.
'Find all data on all sheets!
'Do not search the sheet the found data is copied to!
'List a message box with all the found data addresses, as well!
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 sheet "Input"!
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


[B]If ws.Cells(Found.Row, "I") > 0 Then[/B]

foundNum = foundNum + 1
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
[B]End If[/B]

Set Found = .UsedRange.FindNext(Found)



'Copy found data row to a "sheet" 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
 

pete4monc

Board Regular
Joined
Jan 12, 2014
Messages
53
Hi guys.
I think I've solved it.
If ws.Cells(Found.Row, "I") > 0 Then
Was returning all sheets with dates in I column. So I changed > to ="" and it now seems to be working.
VBA Code:
If ws.Cells(Found.Row, "I") = "" Then

Thanks very much for all your help especially @Joe4
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad you got it working.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,274
Messages
5,635,232
Members
416,848
Latest member
Excel DG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top