Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Search Entire Workbook

Nevermind. I feel stupid now. Just select all the spreadsheets you want to
search for. I tried this, but I forgot the B was part of the custom format,
in order to keep the cell a number.

"Sloth" wrote:

> I am trying to search for specific string throughout an entire workbook. I
> have multiple (50+) worksheets in my file. I want to be able to search the
> entire workbook for any cell that contains B836004. Do I need to open and
> search each sheet at a time, or is there a quicker way?


Post your answer or comment

comments powered by Disqus
Hi all,

I am trying to search my entire workbook, in column C starting with row 2 on each page, for a user defined value. I'd like it to then copy the entire row and paste it to a separate sheet for all matching values. I seem to be hitting a snag after changing my code from just doing "Sheet1" to the entire workbook. Please help trouble shoot.

Thanks in advanced!

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.", "Enter value")

'Start search in row 2
LSearchRow = 2

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

For j = 1 To ActiveWorkbook.Sheets.Count
With Sheets(j)
i = 1
Do While .Range("C1").Cells(i) <> ""

'If value in column E = LSearchValue, copy entire row to Sheet2
If UCase(LSearchValue) = UCase(.Range("C1").Cells(i)) Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Sheet3").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

End If
i = i + 1
LSearchRow = LSearchRow + 1

Loop
End With
Next
'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

I am trying to search for specific string throughout an entire workbook. I
have multiple (50+) worksheets in my file. I want to be able to search the
entire workbook for any cell that contains B836004. Do I need to open and
search each sheet at a time, or is there a quicker way?

Hi all,

Was just wondering if it is possible to search an entire workbook for data which a user has input in say cell A1 on Sheet 1 (or a textbox), and then have them click on a 'search' button located next to the cell to find the data and take them to the cell where it's located? This sounds easy I know but I've spent all day trying to figure out how to do it and not having a great deal of experience with macros am having a bit of trouble!

Hopefully it's clear what I'm after but I'll reiterate:

- Data input by user in cell A1 (or textbox whichever is easier!)
- 'Search' button clicked after data entered
- User is taken to worksheet where that data is located and cell is highlighted

Any and all help very much appreciated.

Lock-e73 ;Ž

Hi, I have a couple of queries.....

Can I set up a group of cells, or an entire column, to always start with the same formula?
Can I use formula to get a cell from an entire worksheet rather than just one sheet?
Can I set up the column to show the formula rather then the contents of the cell?

E.g: I would like if I could just type the cell into column A, and it showed the shortened formula (e.g AA34) rather than that cells contents - is this even possible??? No cell throughout the entire workbook has the same cell numbers.
Ive set up the workbook to list all of a company's stock prices - I'm trying to set it up so that a member of staff can type the cell number (product code) down column A, and the and the quantity into Column B, and column C produces the subtotal.

Row Column A Column B Column C
1 =sheet1!aa412 3 = a1*b1
2 =sheet4!da312 1 = a2*b2
3 =sheet4!ds101 1 = a3*b3
4 =sheet3!cz312 3 = a4*b4
5 =sheet1!aa413 1 = a5*b5

to become:

Row Column A Column B Column C
1 aa412 3 = a1*b1
2 da312 1 = a2*b2
3 ds101 1 = a3*b3
4 cz312 3 = a4*b4
5 aa413 1 = a5*b5

Can anyone help?? Or teach me how to use Access haha

Hi everybody,

Currently I'm working on somesort of 'daily report-system' in VBE. I've created a userform that contains a listbox that displays the information of the active sheet.

The users will use this userform on a daily base and the reports are made by day. So every day they select a date from a calendar to create a new report. The userform will create a new sheet with the date as its name.

But... Now I'm struggling with a couple of issues.

1. I want to use a Combobox to search through the entire workbook and display the rows that match the selected criteria in the Listbox.

Example:

When I select the criteria 'high urgency' it needs to show the following..

Time Event Urgency Status
08.00 Blablabla blabla bla blablabla High Solved
07.00 Blablabla blabla bla blablabla High Unsolved
09.00 Blablabla blabla bla blablabla High In progress
__________________________________________________________________

2. I want the users to be able to add new items/events directly in the listbox by using four textboxes.

3. Is it possible to auto-adapt the rowheight of the listbox to its content?

4. Is it possible to copy the text of selected listbox-items to a new Outlook e-mail. (So we can send events with high urgency directly to our manager?

5. Is it possible to change the status of a selected item by using a combobox? I mean, when a item is solved I would like to set its status to solved by selecting the item and then select the status from a combobox.

I hope someone can help with these issues! Thank you all!

I need to search for the data in column A in one workbook in ALL of the data in another workbook and have it bring back either a LISTED or NOT LISTED in another column. I have tried a couple of functions I have found on-line, but I can't seem to get any of them to work the way i want.

Basically, I need to put a function in column C of Asset Disposal and have it take the info in Column A, search the entire workbook of Dell Lease Inventory (which is too big to attach as it is a MASSIVE workbook), and then return either "listed" or "not listed" in column C of Asset Disposal.

I tried using the function posted on this website, but I can't get it to work:


	VB:
	
 VLOOKAllSheets() 
     
    Dim wSheet As Worksheet 
    Dim vFound 
     
    On Error Resume Next 
     
    For Each wSheet In ActiveWorkbook.Worksheets 
        With wSheet 
            Set Tble_Array = .Range(Tble_Array.Address) 
            vFound = WorksheetFunction.VLookup _ 
            (Look_Value, Tble_Array, _ 
            Col_num, Range_look) 
        End With 
        If Not IsEmpty(vFound) Then Exit For 
    Next wSheet 
     
    Set Tble_Array = Nothing 
    VLOOKAllSheets = vFound 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas or help will be GREATLY appreciated!!!

Thanks!

Hi All,

I was hoping someone might be able to help me, I need to create a search box in my excel workbook that will search for the text entered in an Input Box, and search the entire workbook for it (except the first sheet). Also, for example if I was searching the workbook for "Craig", there might be 4 different Craigs, is there a way I can scroll through the found results?
Any help would be greatly appreciated.

Cheers
Jade

I am needing some code that will search for a keyword throughout an entire workbook and output the total number of instances to a worksheet within that workbook called "result totals"

Is there a function to search for a given word in an entire workbook and
retrieve the information from that sheet into a different worksheet in a
different workbook? I'm fairly comfortable w/ how VLookup works...but am not
able to use it searching a workbood. I'm not very computer savvy...so if you
could really spell it out for me that would help.Thanks!

how do I save settings in a workbook so when i search it selects the entire
workbook vs. an individual sheet of the workbook.
Choices are to right click a tab and select the SELECT ALL SHEETS
or
Use the FIND and go to advanced settings and say search workbook vs. search
sheet.
problem is each time you open the workbook you must go thru this again. how
does one save the settings once you set them?

Does anyone know a way to do a text search in a workbook? This for a
Workbook that has a bunch of worksheets and I'm trying to find away to
search the entire workbook without having to go to each worksheet.

Is there a simple way to do this?

Thanks,
Paul

How can I search for word in the Entire Workbook. Right now I can only do
that by manually switching to a different sheet and searching again for the
same word.

Hi,

I came to a piece of vba but something is wrong with it... Problem is I don't know a solution.

I have a form that searches in an entire workbook for certain keywords, for example I fill in a last name in the textfield txtName and pus the search command button. As a result I get all the rows of the entire workbook containing this name. There are othre search fields in the form that do similar things...

This is the vba code:
Private Sub cmdOk_Click()
 
 
 
 Dim i As Integer, SheetC As Integer
 
 Do

SheetC = Sheets.Count

For i = SheetC To 1 Step -1


       With ActiveSheet
            
        .AutoFilterMode = False
        .Range("A3:K150").AutoFilter

        With .AutoFilter.Range

            If txtLastName.Value <> "" Then
                .AutoFilter Field:=1, Criteria1:="*" & txtLastName.Value & "*"
            End If
            
            If txtFirstName.Value <> "" Then
                .AutoFilter Field:=2, Criteria1:="*" & txtFirstName.Value & "*"
            End If
            
            If txtStreet.Value <> "" Then
                .AutoFilter Field:=3, Criteria1:="*" & txtStreet.Value & "*"
            End If
            
            If txtPostalCode.Value <> "" Then
                .AutoFilter Field:=4, Criteria1:="*" & txtPostelCode.Value & "*"
            End If
            
            If txtCity.Value <> "" Then
                .AutoFilter Field:=5, Criteria1:="*" & txtCity.Value & "*"
            End If
            
            If txtPhoneNumber.Value <> "" Then
                .AutoFilter Field:=6, Criteria1:="*" & txtPhoneNumber.Value & "*"
            End If
            
        End With
        
         
    End With
    ActiveSheet.Next.Select
    
    Next i
Loop

End Sub
The problem is the following line: ActiveSheet.Next.Select

Does anyone have any idea about what is wrong with it? I'm rather new at VBA, so I can't figure it out (yet)

Thanks in advance

T

I want to find a particular cell in the entire workbook which has 5 sheets.

I use the following formula

	VB:
	
Cells.Find(What:="ID12322", After:=ActiveCell, LookIn:=xlValues, _ 
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
MatchCase:=False, SearchFormat:=False).Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Seems like it only searches for the active sheet and not the ENTIRE workbook.

ANyone know the correct code to use ? Thanks!

i have a workbook that contains a series of worksheets. the workbook is a master document list. the first worksheet contains no data. the remaining worksheets are arranged so that A1 has the document number, A2 contains the document title, and A3 contains the review date.

Id like to create a search macro that can search for the string entered into textbox1. if this is a number, the macro should search A1:A50 of all but the first worksheets in the workbook, and if its text, the macro should search B1:B50 of all but the first worksheets.

the macro should then select the cell containing what was searched.

then id like the macro to prompt me and ask if id like to modify the reviewed date to todays date.

id like to be prompted to continue searching for other records if they exist, and after that loop has finished to set the focus back to the search textbox.

hopefully that makes sense. i tried to be specific and to the point, which is why it might sound cold.

thanks

Hi to all. I now having a quest is to create a macro for an excel file which it can search the keyword/string for other workbook where I enter into the text box in a UserForm from the entire workbook. The search may contain date and the product code. Once the macro search out the keyword/string, it will select the whole column which contain the keyword/string, copy it and paste it at entire workbook.

My UserForm is like this:
Capture.JPG
Date Text box = TextBox1
P-Sub Lot Text Box = TextBox2
GET DATA Button = CommandButton1

Hope somebody can guide me with this. Thank you.

I've looked thru the forum for an answer to this but still haven't been able to figure it out.

I'm trying to use vba to find a value in the entire workbook. All the values that I need to search are always in column 3 and are unique so I do not need it keep search once it finds it, especially since there are over 300 worksheets in the workbook. Once the search is complete, I need that range with the value to activate.

The code I have works but I think it is going thru all the worksheets even though it has already found the first address. Also, when the search does not return any results, if of course asks to debug. Any clue on how I'd go about preventing this?


	VB:
	
 clkSearchTarget_Click() 
     
     
    If Me.txtTarget.Value = vbNullString Then 
        MsgBox "Please enter target." 
        Me.txtTarget.SetFocus 
    End If 
     
     
     
    Application.ScreenUpdating = False 
     
     
     
     
    Dim wsname As String 
    Dim R As Range 
     
     
    For Each ws In Worksheets 
        ws.Select 
        With ActiveSheet.UsedRange 
            Set R = .columns(3).Find(txtTarget, LookIn:=xlValues) 
            If Not R Is Nothing Then 
                firstaddress = R.Address 
                wsname = ws.Name 
            End If 
        End With 
    Next 
    Worksheets(wsname).Activate 
    Range(firstaddress).Activate 
     
     
    Application.ScreenUpdating = True 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello, in Excel 2003 there is an option under Edit, Find/Replace, Options to
Search across the entire workbook. I am looking to do this in Excel 2000,
but the option is not there. Is there another way to find/replace across the
entire workbook in Excel 2000?

Thanks so much in advance,
Tif

Hi all,
I am newbie with excel. Situation follows.
I have a workbook with two sheets, each sheet has around 60000
rows(part number) and 6 columns (various prices for coresponding part
number).
This is a price list imported from Access.

1. I need to protect workbook(read only) for distribution and provide
search option for entire workbook.
(i.e. search for part number and result should be entire row
highlighted for that part)

With ctrl-f default option is search "within a sheet" and I need
"within a workbook"as a default.
2. Should (and how) I create third sheet for search option and
results.

Please direct me where to look (books, www) and what to look for,
because I do not
find Excel help very helpfull.
Thanks in advance

--
Mare
------------------------------------------------------------------------
Mare's Profile: http://www.excelforum.com/member.php...o&userid=27644
View this thread: http://www.excelforum.com/showthread...hreadid=471672

Hi all, thanks for looking at my post.
I am trying to put together a spreadsheet that will be used in the emergency room where I work to keep track of patients (a Log book). I would also like to use Excel to help us keep track of stats, since we are already inputting the information.
How can I use COUNTIF to search across the entire workbook?

If someone could explain how to make a User definable dropdown list, that would be great.
I have a smaller version of it that I could email someone, but it is over 200KB.

Thanks
Patrick

Hello, I've been searching hundreds of posts but not found an answer to this. I have a procedure that applies lots of formatting, filters, repaginates and then finally opens the print dialogue box with

Application.Dialogs(xlDialogPrint).Show

What I need it to do is disable / grey out the "Entire workbook" button so that my users can only print out the active sheet. It defaults to active sheet but I just know that someone will change it and get a lot of wasted paper.

According to the help files and other posts, the 'Print what' section is governed by argument 7 but I don't know if this is where to disable an option or what value to pass to do this.

Another option would be to get my procedure to run across all the sheets but this would take quite a bit of time (takes about 30s for one sheet) and the users would think something was wrong even if I popped up a message box telling them it would take a while.

Any guidance on this would be greatly appreciated

Thanks

John

Hi all,
I am newbie with excel. Situation follows.
I have a workbook with two sheets, each sheet has around 60000 rows(part number) and 6 columns (various prices for coresponding part number).
This is a price list imported from Access.

1. I need to protect workbook(read only) for distribution and provide search option for entire workbook.
(i.e. search for part number and result should be entire row highlighted for that part)

With ctrl-f default option is search "within a sheet" and I need "within a workbook"as a default.
2. Should (and how) I create third sheet for search option and results.

Please direct me where to look (books, www) and what to look for, because I do not
find Excel help very helpfull.
Thanks in advance

Hi All,

The crooks of what I want to achieve is to use one workbook with search values in Column A on the first sheet, to search another workbook for all those values.

I have a few workbooks with multiple sheets in detailing a large list of products on each one.

The workbooks are in the same directory as the one I want to use as my Search workbook "searchsheet.xlsx"

My Directory has these files in:-
Searchsheet.xlsx (This is the one I want to use to search and will contain the macro)
Products20-30.xlsx |
Products423-223.xlsx | These contain the products
Products12-24.xls |

On "searchsheet" I have a list of about 100 products in 100 cells in column A (this list of products will change).

I want to create a macro on that book which runs from a VB button that will search a selected workbook (which I can select from the 3above (or more)) for all 100 items in Column A. I want it to search for part contents of cells too, not an exact match.

If it finds any matches I want it to return a value of 1 in Cell C5 and if it doesn't match I want it to return a value of -1.

The idea is that if Cell C5 returns a 1 I will use Conditional formatting to make the cell Red and if it returns -1 then it will turn Green.

Any ideas are greatly appreciated!
Thanks

Morning guvnors

problem : I'm trying to automate the task of converting an entire Excel workbook to PDF but having recorded the action I still get a prompt which I don't want (that's quite poetic isnt it, prompt, want)

In native Excel I would just say

FILE ---> PRINT ---> change the printer to "Adobe Pdf"---> print what: Entire Workbook.

The following code does all this perfectly, but the macro recorder doesn't seem to capture Excel prompting me for the destination to save the file. (I don't want Excel to prompt me as the macro will be part of a larger piece of code that goes into each file in a folder and converts it to PDF so the user can leave it and do something else).

I am happy for the PDF to save in the location that Excel suggests so just want to click "Yes". But not sure how to invoke this part of the code. Here is the code


	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any suggestions please!


No luck finding an answer? You could always try Google.