Follow up, Excel VBscript to search an entire workbook and display all the searched criterias along with the sheet title

[hgbryan9]

Courtesy to wideboydixon for the below code, and as he's busy with other projects and until he can be able to further assist, I'll appreciate the entire community help.

As you can imagine, I'm working on a self-motivated project and greatly need your help.

The code is able to search multiple sheets (that has their name starting with "Zone_" and return one result at a time.

Kindly assist me modify the script to:

#1 : To be able to search multiple sheets within the workbook irrespective of the sheet name, as some sheets are named "Zone_1" , "a.b.c" , "10.10.10.8", etc, etc

#2 : I'll need the search to display all the searched results found on a give sheet along with the sheet title.

Thanking you in advance....

++++++++++start ++++
Public Sub searchdata()

Dim lastRow As Long
Dim thisCol As Long
Dim thisZone As Long
Dim zoneSheet As Worksheet
Dim foundData As Boolean
Dim failedSheet As Worksheet
Dim matchRow As Variant

' Flag indicating whether we found the data
foundData = False

' Work through all zone sheets with the name " Zone_*"
For thisZone = 1 To 100
' Set the sheet reference
Set zoneSheet = Sheets("Zone_" & CStr(thisZone))

' Find the last row
lastRow = zoneSheet.Cells(zoneSheet.Rows.Count, "A").End(xlUp).Row

' Use MATCH() to find a match
matchRow = Application.Match(Sheet2.Range("A3").Value, zoneSheet.Range(zoneSheet.Cells(2, "A"), zoneSheet.Cells(lastRow, "A")), 0)

' Did we find it?
If Not IsError(matchRow) Then
' Set the flag to say we found data
foundData = True

' Copy the data from columns A:F into Sheet2
For thisCol = 1 To 6
Sheet2.Cells(11, thisCol).Value = zoneSheet.Cells(matchRow + 1, thisCol).Value
Next thisCol

' Stop searching - remove this line if you want to keep searching
Exit For
End If
Next thisZone

' If we didn't find it then add a row to the Failed_search tab
If Not foundData Then
' Set the sheet reference
Set failedSheet = Sheets("Failed_search")

' Find the last row and add 1 to go to the next blank row
lastRow = failedSheet.Cells(failedSheet.Rows.Count, "A").End(xlUp).Row + 1

' Log the date and the search term
failedSheet.Cells(lastRow, "A").Value = Date
failedSheet.Cells(lastRow, "B").Value = Sheet2.Range("A3").Value

' Clear the results from Sheet2
Sheet2.Range("A11:F11").ClearContents
End If

End Sub
+++++++++

 

[kbrummert]

You would have to test and debug, but you could try the following quick adjustments to sections of the code to make it do what you are suggesting.

...
' add variables for any sheet name (not just zone sheets), and last row in log sheet (e.g. Sheet2)
Dim sht_name as String
Dim log_sht_last_row as Integer
...



' change so it loops through all sheets, not just zone sheets
For thisZone = 0 To Application.Sheets.Count
' Set the sheet reference
Set zoneSheet = Sheets(thisZone)
set sht_name = ActiveWorkbook.Worksheets(thisZone).Name


...

' adjust so it populates last row in log sheet
Set log_sht_last_row = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row
' Copy the data from columns A:F into Sheet2
For thisCol = 1 To 6
Sheet2.Cells(log_sht_last_row, thisCol).Value = zoneSheet.Cells(matchRow + 1, thisCol).Value
Next thisCol
' sheet name
Sheet2.Cells(log_sht_last_row, 7).Value = sht_name


...



' Log the date and the search term
failedSheet.Cells(lastRow, "A").Value = Date
failedSheet.Cells(lastRow, "B").Value = Sheet2.Range("A3").Value
' sheet name
failedSheet.Cells(lastRow, "C").Value = sht_name

 

[hgbryan9]

Dear All....kindly assist, Courtesy to wideboydixon and kbrummert , but I'm still have few errors with the below code and need your help.

indly assist me modify the script to:


#1 : To be able to search multiple sheets within the workbook irrespective of the sheet name, as some sheets are named "Zone_1" , "a.b.c" , "10.10.10.8", etc, etc


#2 : I'll need the search to display all the searched results found on a give sheet along with the sheet title.


Thanking you in advance....


=================code=================
Public Sub searchdata()


Dim lastRow As Long
Dim thisCol As Long
Dim thisZone As Long
Dim zoneSheet As Worksheet
Dim foundData As Boolean
Dim failedSheet As Worksheet
Dim matchRow As Variant
' add variables for any sheet name (not just zone sheets), and last row in log sheet (e.g. Sheet2)
Dim sht_name As String
Dim log_sht_last_row As Integer


' Flag indicating whether we found the data
foundData = False


' change so it loops through all sheets, not just zone sheets
For thisZone = 0 To Application.Sheets.count


' Set the sheet reference
Set zoneSheet = Sheets(thisZone)
Set sheet_name = ActiveWorkbook.Worksheets(thisZone).Name

' adjust so it populates last row in log sheet
Set log_sht_last_row = Sheet2.Cells(Sheet2.Rows.count, "A").End(xlUp).Row

' Use MATCH() to find a match
matchRow = Application.Match(Sheet2.Range("B3").Value, zoneSheet.Range(zoneSheet.Cells(2, "A"), zoneSheet.Cells(lastRow, "A")), 0)

' Did we find it?
If Not IsError(matchRow) Then
' Set the flag to say we found data
foundData = True

' Copy the data from columns A:F into Sheet2
For thisCol = 1 To 6
Sheet2.Cells(log_sht_last_row, thisCol).Value = zoneSheet.Cells(matchRow + 1, thisCol).Value
Next thisCol


' sheet name
Sheet2.Cells(log_sht_last_row, 7).Value = sht_name



' Stop searching - remove this line if you want to keep searching
Exit For
End If
Next thisZone


' If we didn't find it then add a row to the Failed_search tab
If Not foundData Then
' Set the sheet reference
Set failedSheet = Sheets("Failed_search")

' Log the date and the search time
failedSheet.Cells(lastRow, "A").Value = Date
failedSheet.Cells(lastRow, "B").Value = Sheet2.Range("B3").Value


' sheet name
failedSheet.Cells(lastRow, "C").Value = sheet_name

' Find the last row and add 1 to go to the next blank row
lastRow = failedSheet.Cells(failedSheet.Rows.count, "A").End(xlUp).Row + 1

' Log the date and the search term
failedSheet.Cells(lastRow, "A").Value = Date
failedSheet.Cells(lastRow, "B").Value = Sheet2.Range("B3").Value

' Clear the results from Sheet2
Sheet2.Range("B18:G18").ClearContents
End If


End Sub


' The report page to print a heading "ABC Tech, DNS Entry search result"
' The next line on the report page, should have " Logged on: "the Date and time", and the "computername" of the computer printing the report, plus, the user name typed into the "Report generated by field"
' The content of the report should have the NSLOOKUP, as well as the entire row of each of the searched entries
Sub printdata()
Sheet2.Range("A5:g12").PrintPreview
'Sheet2.Range("A5:g12").PrintOut


End Sub


' The mailto report page should email all the contents on the report page
Sub mailto()
Sheet2.Range("A5:g12").mailto
'Email report to "hgbryan9@gmail.com"


End Sub


' The reset should reset the entire on-screen page
Sub ResetValues()
'ResetValues
Rows("3:3").Select
Selection.ClearContents
Rows("3:3").Select
Rows("18:18").Select
Selection.ClearContents
Rows("18:18").Select
End Sub