Search All Worksheets & Go To Found Values
Aug 30, 2007
I have a workbook with a sheet for every month. I have a searchbox searching for a client and jumping to that location. The problem I am having is that it takes me to the last occurence in the workbook if there is more that one client with the search criteria. Do you have any suggestions for a searchbox that whould take me to the first occurence and give me an option to go to the next occurence in the workbook. Please see the code below that I am using currently using.
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim cl As Range, rng As Range
For Each ws In ActiveWorkbook.Worksheets
Set rng = ws.UsedRange
With rng
Set cl = .Find(Me.TextBox1.Value, LookIn:=xlValues)
If Not cl Is Nothing Then
Application.Goto cl
Else
View 7 Replies
ADVERTISEMENT
Aug 28, 2007
I need a VBA script that can display a search box on multiple sheets within the same workbook similar to using ctrl + F and search values in column B only. If there is text or the row happens to be empty then it should skip that and only search numbers. Also the numbers in column B range from 50000 to 89000 and if there is a wrong number entered then I want to have a pop up box saying Error: invalid value or something like that.
Sub search_box()
Range("B49000").Select
Cells.Find(What:="some#", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
End Sub
View 8 Replies
View Related
Dec 5, 2013
I found some useful code for copying specific worksheets into a new workbook based on the sheet name, which I have not been able to alter to suit my needs.
I have a workbook that has a Master Sheet that contains a summary of each claim (Name, type of claim, dates, dollar amounts, etc.) and a sheet for each specific claim.
I know I can use the filter feature on the master sheet to view all of the physical damage claims (and one column has a hyperlink to the worksheet for that specific claim). But every month I have to create a report that shows each type of claim, dates, and so on.
I would like to create a macro that can copy the worksheets to a new workbook based on the type of claim it is. I'm thinking something like for each ws in wb if range ("F15") = 1 & .range ("B4") < 30 days from today copy that sheet into a new wb.
Below is a generic and far smaller version of my workbook. The name column contains my hyperlinks to the specific sheet. Each sheet is also named based on the text in the name column (so John Doe's sheet would be named 'John Doe') and so forth.
Date of Loss
Name
Cargo
BI
PD
Paid
Reserves
[Code] ........
View 1 Replies
View Related
Jul 17, 2013
I have a excel 2007 workbook that has 5 sheets "MASTER" , "RED" , "WHITE", "GOLD" & "BLUE". There are 7 columns in each sheet and the master has about 8,000 rows . In column D of each sheet there is a unique number (approx. 8 - 10 digits ) that I would like to at the press of a command button search through sheets "RED" "WHITE" "GOLD" & "BLUE" against the unique numbers in the "MASTER" sheet and if there any duplicates numbers delete the entire row but leave all the data in the master sheet.
View 2 Replies
View Related
Jul 14, 2007
I want to enter a value in a textbox, search for it in an worksheet, and populate other textboxes with adjacent values if the value is found. Anyway, the problem is that if the value is not found, I get a debug error.
Dim test1
test1 = TextBox1.Value
Worksheets("data1").Activate
Find_Range(test1, Cells, xlFormulas, xlWhole).Select
TextBox2 = ActiveCell.Value
TextBox3 = ActiveCell.Offset(0, 1).Value
I'm sure there's far better code to do what I need, but I tried to keep it simple. With the above code, it only works if the value is found. If it's not, I get an error. So how do I make it so that if the value is not found, the value of the textbox2 is "Not Found" or something...
View 3 Replies
View Related
Sep 24, 2010
I have a workbook that contains 18 sheets of data. These worksheets are named according to their respective client names (AAA Shine, Fern Barn, Oracle, etc. for example). On each of these sheets, is a column (Column B), that has a salesperson's name in it (Fred, Joe, Ted, Anne... there are about 10 names total). On each sheet, there are corresponding values for the revenue from that account for each month of the year. Those values appear in Column L (Jan), M(Feb), N(Mar),... W(Dec).
What I would like to have is formula that will look at each sheet, find all the instances of Fred for January, sum them, and report them in a single cell on a different sheet I have called "Summary". The end result will be a total revenue from all customers, for each sales person, by month, in one summary sheet.
View 9 Replies
View Related
Aug 21, 2006
As in attached file, I have 5 tables:
Table 1: matrix that contains Weight data in each cell
Table 2: matrix that contains Code data in each cell
Table 3: Relationship between Code and Level data
Table 4: Table with relationship between Weight data, Code data and Level data
Table 5: I want to populate each cell in Table 5 matrix from getting values from Table 4 by looking up the right value from Table 5 based on :
1) Weight data from corresponding cell in Table 1
2) Code data from corresponding cell in Table 2
3) Code and Level relationship.
for e.g. to get value for Table 5, Worksheet cell B140 ( intersection of A1-A1),
1) corresponding weight is 1.17 as in Table 1 (Weight data) (Worksheet cell B3)
2) corresponding Code is 6 as in Table 2 (Code data) (Worksheet cell B35)
3) Code 6 corresponds to Level 2 (as seen in Table 3)(Worksheet cell A74)
so, looking up the 3 values from Table 4 i.e. Level 2, Code 6, Weight 1.17, one gets cell AR98 with corresponding value. therefore, final value in Table 5, Worksheet cell B140 (intersection of A1-A1) will be equal to $4.00
I have been trying to solve this using Vlookup, Hlookup, Index/Match but am getting confused. Also, not too sure if this can be done in 1 step. New to these excel and these functions
View 2 Replies
View Related
Oct 11, 2011
Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
View 1 Replies
View Related
Nov 2, 2009
I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
View 3 Replies
View Related
Feb 8, 2012
I manage to do a proper search and the return value is correct, but the problem is when I don't get the correct value excel gives me an error, what I can add to make the result just to give me MsgBox "Not found"?
Code below:
Set Ran = Worksheets(2).Range("A:A").Find(CompName, lookat:=xlPart)
If Not Ran Is Nothing Then
MatchRow = Ran.Row
MatchCol = Ran.Column
End If
View 5 Replies
View Related
Feb 29, 2008
I am looping in Excel to find "NUMBER OF EMPLOYEES". IF found I want to go up one row and replace the first 4 digits with " 9ZZ". The Line 1 data will alway be different so I just want to replace the first 4 digits. I don't want it to effect the rest of the line.
Example:
Line 1 - 100 6300 BRCH TOTALS
Line 2 - NUMBER OF EMPLOYEES
RESULTS NEEDED:
Line 1 - 9ZZ 6300 BRCH TOTALS
Line 2 - NUMBE OF EMPLOYEES
View 9 Replies
View Related
Sep 5, 2007
I need a VBA code that will search column H for any value that DOES NOT start with "9" (this is a character field). If it finds a cell that doesn't start with "9", I would like it to delete the entire row. It will need to repeat this process for every cell in Column H that has a value and then stop.
View 7 Replies
View Related
Jan 22, 2014
I have data in cells B2:E2 and this can go down 100+ rows.
In column B i have invoice numbers but some cells contain the word "Deposit".
I have sorted this data so that the invoice numbers appear first and then all the Deposits.
I need a code to find the first instance of the word "Deposit" and to insert a row so that all the invoices and Deposits are seperated by a single row.
View 4 Replies
View Related
Sep 8, 2007
In a large spreadsheet that receives external data, I have codes (U, N or V) allocated at various times to different rows as shown in sample worksheet attached. Elsewhere in the spreadsheet, I need to display (for later export) these codes along with their respective number in a sorted list. Please review the attached:
In the spreadsheet you'll see a column of codes, the next column is the data reference number, then to the right is three columns, one for each code. As displayed in the sample book attached, each Code column is to display the data reference number (from column 2)that matches that column's code.
What formula can I use to list these numbers in the appropriate columns?
View 5 Replies
View Related
Oct 18, 2007
I was looking at this VBA code the other day and i was wandering what i meant. I thought if somone could expliane line by line,
Sub CommandButton1_Click()
Dim ThisAddress$, Found, FirstAddress
Dim Lost$, N&, NextSheet&
Dim CurrentArea As Range, SelectedRegion As Range
Dim Reply As VbMsgBoxResult
Dim FirstSheet As Worksheet
Dim Ws As Worksheet
Dim Wks As Worksheet
Dim Sht As Worksheet
View 6 Replies
View Related
Sep 19, 2009
I have two workbooks, one is used for importing items to the site while the other is a monthly product list. In each of these I have a list of sku codes. I need a macro that will search each sku from the import to the entire product workbook. If it's found then delete the whole row from the product workbook. I have attached the examples below.
View 6 Replies
View Related
Oct 26, 2012
I am using excel excel 2007 in windows 7. I have a search button that works great, however, I want it to also highlight the cell green, when it finds what I am searching for. Here is the code below for the search button I have.
Private Sub CommandButton6_Click()
Dim searchthis As String, Found As Range
Me.Unprotect Password:="123"
searchthis = InputBox("Type Number.", "Property Search")
searchthis = searchthis & "*"
Set Found = Range("A:A,e:e").Find(What:=searchthis, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not Found Is Nothing Then Found.Select
Me.Protect Password:="123"
End Sub
View 9 Replies
View Related
Aug 13, 2007
I have the following code to search for serial numbers.
Private Sub CommandButton1_Click()
Dim Message, Title, Default, SearchString
Message = "Enter Serial Number" ' Set prompt.
Title = "Find Serial Number" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)
'SearchString = "Rob"
Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set f = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If Not f Is Nothing Then
f.Offset(, 3) = Date
Exit For
End If
End With
Next S
End Sub
I would like to amend this so that (a) if the serial number is not found I get a message box saying "Serial number not found" and (b) if the serial number is found, I would like it to highlight the relevant row (after inserting the date).
View 9 Replies
View Related
Feb 16, 2008
i have a workbook with several sheets in it. i would like to make a userform were i could put a number in a text box eg E045698.then use a command button to search all the worksheets for that number and display the sheet number where that number is in another textbox.
View 5 Replies
View Related
Feb 12, 2014
I am trying to search through column "I" for numbers that are decimals, when the first decimal is found, use the row number that the first decimal appears in the string that populates the bookmarks stored in a word template I have created. The code I have so far checks for the decimal and then populates the bookmarks, however it requires me to input the ranges manually, meaning I can set them as the first row in my tabel. But if the decimal appears in the second row and not the first I'm bookmarking the wrong data. Here is the code I have so far;
[Code] ........
View 3 Replies
View Related
Jul 7, 2009
I've attached a dummy worksheet that shows constant values of 1.00 in columns D and E. In the actual workbook, user action will sometimes cause these values to change to something other that 1.00. The user may wish to delete the action that caused the change.
I need a macro that will search from the last cell in column D upward to the first instance of a cell that is not equal to 1.00 and select that cell and those immediately above with the same value as the first found cell.
For example, in the attached worksheet the search would start in the last cell of Columd D and search upward. It would find cell D23 and upward to D19. These entire rows would be deleted.
View 5 Replies
View Related
Dec 29, 2011
I have written a macro to convert data from a report exported from Salesforce.com to a format that is acceptable for upload to our website. One of the necessary conversions is to convert values from a Boolean value to a Y/N value. I wrote a macro with a series of search-replace commands such as this:
Code:
'replace 0s and 1s in "Direct Billing" column with appropriate data
Columns("R:R").Select
Selection.Replace What:="0", Replacement:="N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
This code generates a warning when no "0" values are found:
"Microsoft Office Excel cannot find any data to replace. Check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet."
View 2 Replies
View Related
Dec 7, 2006
Is it possible to search a worksheet that may contain different number of rows (from day to day) and for every instance of a word like "HOLD" is found in column "C" to Color that intire Row Red and also Bold the Row as well?
View 9 Replies
View Related
Jul 18, 2012
I have been able to make a excel sheet which takes inputs from userform for First Name, Last Name, Address etc. I have included a duplicate check for column "B" for "First Name". This checks if any existing data is already which matches the new data input for "First Name" through userform.
The userform only checks for the "First Name" check as required and gives a message that duplicate has been found. Then I have to close the userform and do a Control Key+F ( to find the new name for example, James) in excel sheet and validate that new name is same or different from existing name "James". This I want to do since this new name "James" may be another "James" as his "Last Name" is different. So even though First Name is same, since Last Name is different I know they are two different persons. In that case I will add the entry manually in sheet, instead of userform, since I would not be able to input the new data for "James" since the duplicate check with the current code will not allow me to do so.
What I am current trying is -
1. If the new name say "James" is entered through userform, then excel should point me to the existing row where the record for existing name "James" is there, say row 4.
2. Now without closing userform I should be able to see in the background excel sheet the search results for "James", as excel is pointing to that now. There may be multiple "James" in the existing which should be pointed out.
3. Based on the results that I see in the background excel sheet I can now decide that, this new name "James" is different from old "James" (of row 4) since his Last Name is different. Accordingly excel code should then ask me to add this record or discard this new record.
4. Duplicate check for First Name is enough for me. I would not require "Last Name" duplicate check.
I hope I have been able to explain my problem. I have also attached my current code as I am not able to attach any sample test file.
Code:
Private Sub cmdCancel_Click()
Unload Me
End Sub
[Code]....
View 9 Replies
View Related
Jan 17, 2013
I have two sheets with data. I wont excel to look for a value in colum A in sheet 2 the value to look for is defined in colum a in shet 1. If value is found it should insert in sheet one the value that is in the cell next to the found value. The data is not sorted. I have attempted and failed with using different functions.
View 1 Replies
View Related
Sep 26, 2012
I need to compare 2 worksheets, one containing a list of IP adresses that need to be checked (interfaces), and another containing a list of IP addresses that have been checked (Scanned hosts), there are over 100000 scanned host entries.
I need to search the "Scanned hosts" worksheet to ensure that all of the IP addresses listed in the "Interfaces" worksheet have been scanned. I then need to place an entry next to the IP address on the "interfaces" worksheet to indicate the check date and the name of the person who checked it. It also needs to change the colour of the cells to indicate if it has been found or not found.
The code I have written so far (from piecing together different threads from MrExcel), at first glance, seems to work perfectly. However, if I replace an ip address in the "interfaces" worksheet, with one that I know does not exist - it still marks it as found.
Interfaces;
IP Address
System
Date Checked
Checked By
1.2.3.4
fred bloggs
1.2.3.5
fred bloggs
[Code] .....
Scanned hosts is just a list of IP addresses and text (168000 rows) in a single column. Format is xx.xx.xx.xx ^IP^^^^Jul 29 2012 08:01:29:000PM
Sub Compare()
Dim X As Long, CurrentRow As Long, FindRow As Long, WS1 As Worksheet, WS2 As Worksheet
Set WS1 = Worksheets("Interfaces")
Set WS2 = Worksheets("Scanned Hosts")
CurrentRow = 1
[Code] .....
View 4 Replies
View Related
Jul 8, 2014
I have a file which has in excess of 12,000 rows of data in 5 columns (sample file attached with fake data). The five rows are:
"First Name" "Last Name" "Name" (uses CONCATENATE to combine column A & B) "Email" "Date Attended"
There will be duplications in the list as people will have attended more than once over the years.
What I want to do is search through the email addresses (Column D with D1 being the header) and where there is a duplicate email address copy the cell to the right of the duplicate (F#) into the next available cell to the right of the first occurance and then delete the row with the duplicated email address.
I am on Windows 7 and Excel 2010
View 4 Replies
View Related
Mar 4, 2014
I need one formula to copy and paste the checkcard data changing it from this "CHECKCARD XXXXXX5623" to "CHECKCARD 5623", but I need one formula to look for multiple known checkcard numbers. I will have more then one typically in the bank download so I need to search and copy at least two known numbers as shown below.
I am providing the formula that I am using right now, "=IF(ISNUMBER(SEARCH("5623",B3)),RIGHT(B3,41),"")" but it only works for one checkcard number? Also I don't really need to capture the DEBIT or CREDIT that will always be at the end, it would be redundant. If it's easier to write the formula including that data that is no problem. What I want to copy and move into an adjacent cell is in red.
Here is the source data:
Row 2 - SONIC DRIVE IN#1531 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX5623 DEBIT
Row 3 - JASON'S DELI # 125 Q64 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX9718 DEBIT
View 4 Replies
View Related
Sep 3, 2009
I need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I started on the code below but am stuck.
View 14 Replies
View Related
Oct 30, 2008
I have a problem with finding the value in column B that is found within a range eg: <=1 & >=5 and return the corresponding values found in column A. kindly refer to the attached sample for reference.
View 2 Replies
View Related
