create a search string

Microsoft Office excel 2007 home & stude...
June 21, 2010 at 11:01:14
Specs: Windows XP, e7200
I currently have an excel file with 15 tabs. What i would like to do is to create a 16th tab, with a main search box that i can type in a zip (ex: 75042), and it will search all 15 tabs for that zip.

Currently i have to click on each tab, and use the search box on each tab to locate the zip and the corresponding city.

Thanks you in all advance, for your assistance.

See More: create a search string

Report •

June 21, 2010 at 14:00:03
re: ...and it will search all 15 tabs for that zip.

What do you want to have happen when it finds the zip?

What do you want to have happen when it doesn't find the zip?

Where is the data that you searching for? In a specific Column? A specific Row? Anywhere on the sheet? etc.

Report •

June 21, 2010 at 14:31:47
currently i have a worksheet with zip codes in column "W" and the city name in column "Y". When you type in for example - 75042, the formula i have will display a GREEN box the zip is in, and the city name next to the box.. I would like to have this happen from a main worksheet, versus clicking on each individual tab/worksheet then searching for the city name.. currently if there is no corresponding zip or city it shows "invalid". here is the formula..

=IFERROR(VLOOKUP(C5,'Albany Market'!W:X,2,FALSE),"Not Valid")

Report •

June 21, 2010 at 16:39:53
One possible solution is a VBA Change Event macro.

Assuming you are entering the Zip in A1 of Sheet 16, this code will monitor Sheet16!A1 and when it changes, loop through all of the other sheets looking in Column W for the Zip that you entered. If it finds it, it will put the city name in Sheet16!B1.

If it doesn't find the Zip, it will return Not Valid in Sheet16!B1.

(You can eliminate the need for "Not Valid" by using a Data Validation Drop Down list referring to all of your Column W's so that it will only (and always) contain just the zips from your other sheets. All you would need to do is choose the Zip that you are looking for from the Drop Down)

To use the code, create your 16th tab as the last sheet in the workbook. Right click the sheet tab for the "16th" sheet and choose View Code.

Paste this into the window that opens and enter a Zip in Sheet16!A1.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim shtNum As Integer
Dim zip As Range
'Determine if change was made to A1
 If Target.Address = "$A$1" Then
'Diasble Events
  Application.EnableEvents = False
'Loop through all sheets except the last one
   For shtNum = 1 To Sheets.Count - 1
'Search for the Zip Code entered
    With Sheets(shtNum).Columns("W")
     Set zip = .Find(Target.Value, lookat:=xlWhole, LookIn:=xlValues)
'If found, put city name in B1 and leave loop
      If Not zip Is Nothing Then
       Target.Offset(0, 1) = Sheets(shtNum).Range("X" & zip.Row)
       GoTo Done
      End If
    End With
'If zip is not found, display Not Valid in B1
    Target.Offset(0, 1) = "Not Valid"
 End If
'Re-enable Events
    Application.EnableEvents = True
End Sub

Report •

Related Solutions

June 21, 2010 at 17:03:14
Excellent - thanks - i will try this first thing in the morning, and will let you know..

Report •

June 22, 2010 at 07:10:33
DerbbyDad03 get 5 stars *****
You are the Bomb-Diggity.. It worked perfect.. Thank you SO much for your assistance.. I am fairly new to learning excel formulas and have made a few strings, but nothing this fancy..
Once again Thank You very much for you help.!!

Report •

June 22, 2010 at 07:43:05
Thanks for the Kudos!

I just want to clarify your use of the word "string".

What I offered is not a "string". It is a known as a Macro, witten in a language known as VBA - Visual Basic for Applications.

In addition a formula is not a "string".

In the programming world, a "string" has a specific definition:

"a linear sequence of symbols (characters or words or phrases)"

In VBA it gets even more specific. This is from the VBA Help files:

"String Data Type

There are two kinds of strings: variable-length and fixed-length strings.

A variable-length string can contain up to approximately 2 billion (2^31) characters.

A fixed-length string can contain 1 to approximately 64K (2^16) characters."

In your case, the Zip Code you are searching for can be considered a "string" and the city name that it returns is a string, but the formula or macro that does the searching is not a "string".

Report •

June 22, 2010 at 07:57:14
Ahhhhh I gotcha - funny thing is i have worked on computers (80% hardware, 20% networking) for over 20 years. I never bothered to worry about this kind of stuff, so my language is a bit off..
I once again thank you for the explanation, this will definitely make it a lot easier when reading through these forums, and my books. Your explanation helps immensely.
I actually have a couple of lines i need to add to the VBA macro you created, but now that you have done 98% of the job, i am attempting to teach myself the last 2%. This way i can back-track your 98% and learn what it is each line/command does.

Report •

Ask Question