Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Fast Search Entire Excel Workbook & Return All Results


Fast Search Entire Excel Workbook & Return All Results into a Dashboard 

how to fast search through this Field name 1) Delivery  2) Invoice No  3) LR No.in entire excel workbook with vb code 



Selected Answer


Thanks for the response above (though somewhat delayed!) .

To do what you want, I've modified  the attached file to add the VBA module from the tutorial  Fast Search Entire Excel Workbook & Return All Results and made these changes:

  1. Added the Dashboard sheet from the tutorial and changed headings in row 10 to match those in your sheet1
  2. Modified the List data validation in cell E4 to:"Delivery, Invoice No, LR No."
  3. Changed the VBA code as follows (changes and comments in bold:
Sub Data_Search()
' TeachExcel.com
' modified for 3 columns

Dim ws As Worksheet
Dim dashboard As Worksheet
Dim dataArray As Variant
Dim datatoShowArray As Variant

'Application.ScreenUpdating = False 'Turning off for the macro can speed things up - not so much here though.

'Dashboard sheet
Set dashboard = Sheets("Dashboard")

'Data table information
dataColumnStart = 1
dataColumnEnd = 9
dataColumnWidth = dataColumnEnd - dataColumnStart ' Number of columns for the raw data (+1 not included because it makes lower calculations more confusing)
dataRowStart = 2
dashboardDataColumnStart = 2 ' Column for the data on the dashboard

'Get user input
searchValue = dashboard.Range("C4").Value
fieldValue = dashboard.Range("E4").Value

'Clear Dashboard
Call Clear_Data

'Figure out by which field we will search.
If (fieldValue = "Delivery") Then
    ' make it column 4
    searchField = 4
ElseIf (fieldValue = "Invoice No") Then
    ' needs column 5
    searchField = 5
    ' then assume it's Lr No., column 8
    searchField = 8
End If

'Loop through the worksheets
For Each ws In Worksheets

You can now use the Dashboard selections to search sheet1 (and any others you add with the same format).

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.



You are amazing, Thanks a lot for help me.
rjitsoft (rep: 2) Sep 2, '23 at 12:56 am
Glad that worked but you marked Willie's Answer as Selected, not mine. 
John_Ru (rep: 6212) Sep 2, '23 at 1:34 am
bymistake select Willie Answer. once again thanks for quickly responce.
rjitsoft (rep: 2) Sep 2, '23 at 7:50 am
Thanks for correcting that!
John_Ru (rep: 6212) Sep 2, '23 at 8:14 am
Add to Discussion

Hi rjitsoft and welcome.

It's good that you provided a "demo" file, but I need a bit more clarification. In your demo file, is that sheet a sample of a worksheet to search or the dashboard? Do you want the search to include all 3 criteria (Delivey, Invoice, and LR No) or jsut one? Do you want the search to return only the 3 criteria or the entire row of data?
Performing a search of the entire workbook is possible, just need to know the details.

You may find Don's macro example ( on this site ) helpful:  Search Entire Workbook

If Don's macro does what you need please mark this answer as selected

Cheers   :-)



@Willie- I removed my discussion point under the Question (since your removal meant it made no sense, as does yours now!). From the Question's title, I'm pretty sure that the user has found the the tutorial Fast Search Entire Excel Workbook & Return All Results   or the one you pointed to and wants someone to modify the VBA code to do single searches on the three parameters stated in the Question. Hopefully that will be clarified.
John_Ru (rep: 6212) Aug 23, '23 at 10:44 am
Thanks for respond,
i want to search this (Delivey, Invoice, and LR No) any of just one at a time then show which one search then releted column show Row wise all field data. like
if i search Invoice the Invoice releted show data Row wise.
rjitsoft (rep: 2) Aug 31, '23 at 3:52 pm
Please see my Answer, just added.
John_Ru (rep: 6212) Sep 1, '23 at 3:40 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login