VBA- Searching Through Multiple Wksheets/workkbook

May 13, 2009

I need to do the following:I have multiple worksheets/ workbooks that hold user info such as address, country, first name,last name, ID,dob, gender, occupation etc
I need to make a search dialog whereby the user is allowed to do this:

1-Search by ID by selecting thru the calendar tool OR

2- search by first and last name. When he clicks search, I need the particular worksheet holding the data to pop up and show the user the data that was requested for.All data in ID column is unique...but for first n last name, i have a few people who have identical names

View 2 Replies


ADVERTISEMENT

Searching Multiple Fields

Sep 27, 2009

I am fillin out a spread sheed with a num ber of codes. I want to create a excel formula that will look at the entire column and search for each of the codes. If I have forgotten to add one of the codes into the column I need the formula to let me know.

What I was thinking might work is this...

To look for one of the codes I would do this.
=IF(MATCH("FLA",E5:E36,0)>0,TRUE,FALSE)

This works great if the Value is in there. If it is not in there I get #N/A instead of FALSE. Is there a way to get the MATCH to return another value if it is not found in the range?

Is there an easier way to look for multiple values? So far the only solution I have found is like this

=IF(LEFT(Q19,1)="D",7.5,IF(LEFT(Q19,3)="FLB",7.5,0))

For each value I need to look up I add another IF function. I have about 17 different values I need to look for. This makes for a ugly function but it seems to work.

View 9 Replies View Related

Searching For Multiple Data

May 24, 2006

I have been using the VLOOKUP function to search a table of data for a specific value in order to then give me the corresponding data associated with that value. I can do this and it works!!

However, as I am searching for names some people have more than one set of data and the VLOOKUP only finds either the first set of values (if the FALSE command is used for ‘range_lookup’) or the last set of values (if the TRUE command is used for ‘range_lookup’). I have attached a small example:

How can I get all values to be displayed?

I know that this may have been explained horrendously but I have written and re-written this several times and this is the best I can get!

View 9 Replies View Related

Searching Multiple Workbooks

Sep 29, 2006

i am trying to set up an interface through Excel for a database of record also held on excel. I have very very basic VB skills and have no idea what im doing lol but what i would like to do is have a search box and button, something similar as you would find on most websites. I would also like (if possible) for it to search up to 8 workbooks to look for a name or vaule.

View 3 Replies View Related

Searching For Multiple Text Strings?

Aug 12, 2014

I want to search a longer text field for a shorter string that may be within it. However, I want to do this for multiple short strings.

For instance:

PhraseHidden word
Please search the text for the hidden message: Blue
The hidden word, Green, may be at any point
And the word may be different lengths like Yellow

I have a table of the hidden words (Blue, Green, Yellow, Pink)

I know I could use search to find one word, but is there a way to look for multiple words, and return the value of which one it finds? I have many hidden words (and the list is dynamic) so I don't want to just split into multiple searches.

View 5 Replies View Related

Searching For Multiple Criteria In Array

Nov 1, 2009

I have an array of data that i need to find multiple data points in: ...

View 11 Replies View Related

Searching Multiple Columns On Same Sheet

Mar 26, 2009

Fall - Columns A-G
Spring - Columns I-O
Summer - Columns Q-W
Unduplicated - Columns Y-AD

I am needing to take each student in the Unduplicated section and see which terms they attended. If they attended a specific term, I need to grab a piece of data from that term and put it in over in the undulicated area.

The only way I could think to do this is with three separate searches. This is probably grossly inefficient and so far it has not worked at all. I have posted below the code I am currently using. Again this code is not workin at all.

View 2 Replies View Related

Searching Table When Value Has Multiple Results?

Feb 2, 2014

i have a spreadsheet that i use for work which has 1 page that contains all the data for the workbook. on other pages i use this data, and tables, to pull information to those pages or other pages which complete my work in a more timely fashion.

that issue i'm having is when i try to look up specific values within the data page tables and there are multiple values.

i need a way to choose different values rather than the "first" value in the instance of a vlookup which only finds the first value.

in the pictures i have picture 1 (vlookup samples) has a section where i can type in the "office" code and it will auto pull "switch name" and "switch clli" via vloookup.

in picture 2 (switches sample) i have created a table, which is on another page as stated above, but if i try to search for the example "LSGT5" it only returns a switch name value of "LFTYINXFDS0" and there is also a value of "LFYTINXFDS1".

there are many more within the table(for this office code alone) but this is just an example of how i need a way to choose the proper one or somehow have a drop down without having to create a bunch of tables for each office itself.

View 6 Replies View Related

Searching Returning Multiple Matches

Mar 6, 2007

I am looking to perform a search that will return a match and place it somewhere else, and then search again until its finished the list?

View 9 Replies View Related

Searching Multiple Worksheets And Retrieving Data

Aug 27, 2009

I have two lists. One is a database (Worksheet 2) with a lot of information, and another is the list (worksheet 1) I need to fill up the data for. What I want to do is to search for the names that are in worksheet 1 in worksheet 2, and if there is a match, retrieve the missing data in those rows, and paste it back into worksheet 1. For example:
Worksheet 1: Joe
Tim 23
Matt

Worksheet 2: Alex 45
Joe 23
Mark 47
Tim 44
Matt 24

I basically want it to be able to search for the corresponding name, and then pull the number associated with it.

View 2 Replies View Related

Searching For Multiple Characters Within Range Of Cells?

Dec 10, 2013

The following code works fine to determine if a particular character occurs within the selected range of cells:

Code:
Sub CheckIfCharacterIncluded()
For Each MyCell In Selection
If InStr(MyCell.Formula, "#") Then
MsgBox ("The " & "#" & " character was found in cell: " & MyCell.Address & " at position " & InStr(MyCell.Formula, "#"))
End If
Next
End Sub

However, I would like to extend this functionality to check for multiple characters, using some sort of array that contains all the characters I want to check for e.g. "#","*","£" and so on, without having to repeat the above code for each character for which I need to check.

View 2 Replies View Related

Multiple If Statements And Searching Blank Cells

Apr 2, 2014

How can I create a multiple if statements that returns a specific response if the cell is blank?

For instance, =if(K6=[date],"Carry Out"), if(K6=[blank],"Break Out"))).

View 3 Replies View Related

XL Macro For Searching Column For Multiple Entries

Jul 8, 2014

The code I currently have allows me to search for one text entry in the column and then copy and paste all those entries into the next worksheet. There are 5 different text entries I'd like it to look for but can't figure out how to do it. I have a work around by using wildcards, but then I have to write another macro to delete out the ones I don't want.

Where is says "DNA - weapons", I'd also like it to allow for "DNA - paternity" and a couple other options.

Here is my current code:

Sub Copy_To_Another_Sheet_1()
Dim FirstAddress As String
Dim myArr As Variant
Dim Rng As Range
Dim Rcount As Long
Dim I As Long
Dim LastCol As String
Application.ScreenUpdating = False

[Code] ....

View 2 Replies View Related

Searching For Values Based On Multiple Criteria

Jul 20, 2009

I have a problem with excel that I have been trying to solve for the past 5 days! I have 2 tables (one with 500 rows, the other with 15000) that contain information about pipes.

The smaller table, Drift List, is to be used as a reference to fill up the bigger table.
Here is how Drift List looks like:

Size NominalWeight WallThickness APIDriftDiameter AlternateDriftDiam.
5.5 14.00 0.244 4.877 7.625
5.8 14.88 0.253 4.915 ----
...

The other table, Pipe Database, Looks like:

Size NominalWeight WallThickness DriftSize DriftType
5.5 14.00 0.244
...

I have to fill up Drift Size using APIDriftDiameter from the first table, BUT if AlternateDriftDiameter is available, I have to use that instead.

DRIFT TYPE column has to indicate either API or Alternate (Depending on which one I have used).

Here are the two files:
[url]
[url]

View 9 Replies View Related

Wildcard Searching For Multiple Values And Returning A Result?

May 27, 2014

I have been trying to find a formula for this but I have yet to find one that works for me.

Data;

English - United Kingdom, Czech, Spanish
UK - United Kingdom
Swedish
English - US
French
US
France
German
Portuguese
+8000 more various values

I am looking for a formula that can search for multiple values e.g. english, us, uk and united and then return the value "Yes".

I was looking for something that includes wildcard searches or contains so that it is not affected by upper or lower case but combining this with multiple searches was proving difficult.

View 7 Replies View Related

Searching For Specific Text Across Multiple Columns In A Sheet

Sep 25, 2013

If I'm using this function to find the specific text "EXPIRED" in the specified cells, how can I add additional cell ranges on the same sheet to this function?

D17:D34 as well as F17:F34

=IF(ISERROR(MATCH("EXPIRED",Homepage!D17:D34,FALSE)),?"OK","ALERTS")

View 2 Replies View Related

Looping Through Files In Multiple Folders Searching For Certain Values

May 1, 2013

I'm trying to code a macro to search through all the files in certain folders to find a value defined by the user. The rows containing that value will then be copied and pasted into a separate workbook. My boss currently planning on storing about 550 different files (90 days worth of data) between these folders, but there is the possibility of years worth of data collection, should he change his mind about the 90 days, so I'd like the code to be efficient if possible. What I've tried to do is search each file for the value, then set a range equal the first row containing that value. If the value is found in that workbook, the code loops through the worksheet, adding all the other rows with that value to the range. Then the file looping exits (the values I'm looking for are only contained in one of the workbooks) and the range is copied and pasted into the master workbook. Each workbook has only one sheet.

I'm currently getting a run-time error "13" Type Mismatch error when it gets to the line where the code is supposed to find the value and initialize the range.

Code:
Private Sub SubmitButton_Click()

Dim mybook As Workbook
Dim masterbook As Workbook
Dim rownunm As Long
Dim pathparts(1 To 5) As String

[Code] ......

View 3 Replies View Related

VBA - Searching Multiple Worksheets And Pasting Results In Single?

Jan 17, 2014

I have multiple worksheets with part numbers and prices for different computer parts. On each worksheet I have multiple sets of part numbers and prices but I only need one the information from specific part from each worksheet. Luckily, the part number I need is also the name of whatever worksheet contains it. I want to take the part numbers and prices from the parts I need and put them in one master sheet called "PartsNumbersCombined". Currently I haven't even attempted to format the data in PartsNumbersCombined, I am just trying to actually pick up and move the correct data. Here is my code:

Sub harvest()
ShtCount = ActiveWorkbook.Sheets.Count
For i = 2 To ShtCount

[Code].....

The part numbers are in column A and the prices are one row down in column C. I feel like I am close but I can not get anything pasted on my master sheet.

View 1 Replies View Related

Macro To Open Specific File By Searching Multiple Folders

Oct 11, 2013

I am trying to write a macro to open a specific file, but need to search multiple folders within folders to find it.

The file name I need to open is "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx".

I need to drill down to the Adherence Report folder and then have the macro search through folders for each year (2012, 2013, 2014, 2015, etc) and then each month within each year (1 January, 2 February, 3 March, etc), at which point I would then find that day's file.

So far I have the following:

Code:
Sub Open_ESCL_Report()

Workbooks.Open Filename:=*****.****.****.******.comsharesPurchasingTeam XEscalationAdherence Report & "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx"
End Sub

Each file is stored in it's respective month folder as .....Adherence Report(Year)(Month)(File).xlsx

View 3 Replies View Related

Macro - Select Multiple Cells By Searching For Specific Text

Nov 8, 2013

I would like to select all cells in column A that begin with the text "SP". Some cells will be contiguous but others will not. For instance in one case, I would like the macro to select cells with the text "SP" which would result in cells A1, A2, A3, A10, A15 being selected. I am working on building a macro that will then do other things to these cells/rows so this is the first step.

The below code will select the first cell with "SP". How would I alter this code to only search Column A and select multiple cells? or totally different code.

Code:
Sub test()
Dim r As RangeSet
r = Cells.Find(what:="SP", LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then r.Select
End Sub

View 7 Replies View Related

Searching Multiple Worksheets For A Date And Pasting The Entire Row Onto A New Worksheet

Jun 24, 2008

I have been searching the boards long and hard for this solution and have yet to find that applies fully. Every day I need to search through 20 worksheets for transactions occurring on a specific date and copy the row onto a new worksheet. I would like to run a macro that would allow me to search for a specific transaction date in column C of each worksheet and if the date matches it will copy the entire row to a new worksheet.

The issue I am having is that the transaction lists are a running sum of all the transactions for each account. Therefore, the range that the macro needs to search will change daily as well. Furthermore, the date that I would be searching for could potentially appear in column A or B but I only want the row if the date matches that of column C. Also, on some sheets there may not be a transaction at all.

View 9 Replies View Related

Copy And Rename Files In VBA By Searching Multiple Folders For Files?

Jan 4, 2014

how I can loop through folders to select files starting with a certain word and copy all of them to a different folder and rename them. The folder structure is given below

Company 1(parent folder)
North South East(sub folder) West(sub folder)
Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec

In the above structure, the files are present inside each folders Jan, feb...Dec under the regions North, South East n west. note that I have to select files starting with "Sales" and copy them into a new folder(say results) and rename copied files as Sales1.xls, Sales2.xls etc. (Files are not present in the folder company1, north, south, east and east.)

View 1 Replies View Related

Searching For Text

Oct 13, 2009

I have doc with app 1000 rows of data, one col being product description. I want to simply search the entire description column for a particular string of characters and enter a 1 in a new adjacent column where there is a match. Where there is no match, I want a 0 or a blank.

View 3 Replies View Related

Searching Within A Cell

Apr 24, 2007

I'm wondering what function I can use to search a specific word in row with many text?...

i attached for the example and details.

View 10 Replies View Related

Searching A Cell

May 10, 2007

I am trying to create a search facility to search for accreditations.

currently my search only picks up on the first word in the cell.

ie Prince will be found in Prince 2 Foundation, but a search on foundation comes up with nothing.

How do i get the search to search all the text in the cell and not just the first word.

I am searching on a number of worksheets but in the same range on each worksheet.

View 9 Replies View Related

Searching Summation

Jun 26, 2007

I need to be able to search through category(column H) and match with machine size category. Then for each category split up total time spent on each function. Granted this will be 6 formulas. I've searched through the forums and came up with what I have....

View 10 Replies View Related

Searching Various Columns

Aug 14, 2008

I have 180,000 names in a spreadsheet with unique codes for each, I need to search for names and find their code numbers, how can I do this over a series of 8 columns.

Code Name code Name code Name Code Name

I'll need to search columns 2, 4, 6 and 8 for the name?

View 9 Replies View Related

Searching By Date

Mar 3, 2009

Have the following scenario I'm struggling with in Excel 07:

2 worksheets:

WS1, have a bunch of customer data. Relevant columns in this WS include customer number (unique value) and a column called "adjustment date", which is formatted as a date as the label name implies.

WS2 is a bunch of customer transactional data, with each row being a unique transaction. Relevant columns here include customer number (formatted same as in WS1) and transaction date.

Couple things to note: in WS2, each customer may have dozens of transactions. Right now, I have that WS sorted by transaction date in ascending order (so oldest to newest).

What I'm trying to do (unsuccessfully so far!) is build a formula in WS1 that says "on what date was the customer's first transaction after their adjustment date"?

I've tried constucting something using =index+match, but to no avail.

View 10 Replies View Related

Searching Tabs

Jul 24, 2009

I have a workbook with 100+ tabs(not in alphabetical order), and am constantly scrolling back and forth looking for tabs, is there a quicker way to search tabs? I have also right clicked the arrows to pull up the box with all tabs, but not the best option either.

View 7 Replies View Related

Searching For Duplicates

Feb 3, 2007

Just trying to find a formula to look for duplicate entries in the same workbook or a single column

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved