Searching data in a column via Input Box

Hello I am new to Excel VBA and think I have thrown myself into the deep end 
by trying to produce an advanced spreadsheet.

What I am trying to do is have a button on my spreadsheet that when clicked 
opens up an Input Box, the user can input some data and then it searches a 
column for the matching input.

For Example

Input Box > "Hello"

I would like it to then search column A1:A20 for the Word Hello. Once found 
just leave the cell selected and a message box saying the word has been found.

If anyone could do this for me I would be very greatful I have tried now for 
2 days and finding it really tough.

Thanks for the help
0
Chris6982 (632)
4/28/2006 9:29:02 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
512 Views

Similar Articles

[PageSpeed] 25

Chris,

Try some code like the following:

Dim FoundCell As Range
Dim WhatToFind As String
WhatToFind = InputBox("Find What?")
If WhatToFind = "" Then
    Exit Sub
End If
Set FoundCell = Range("A1:A20").Find(what:=WhatToFind, _
    LookIn:=xlValues, lookat:=xlWhole)
If Not FoundCell Is Nothing Then
    FoundCell.Select
    MsgBox WhatToFind & " found at " & FoundCell.Address
End If


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Chris" <Chris@discussions.microsoft.com> wrote in message 
news:EADBBF89-80DC-491A-AE6F-58ED91C4DC22@microsoft.com...
> Hello I am new to Excel VBA and think I have thrown myself into 
> the deep end
> by trying to produce an advanced spreadsheet.
>
> What I am trying to do is have a button on my spreadsheet that 
> when clicked
> opens up an Input Box, the user can input some data and then it 
> searches a
> column for the matching input.
>
> For Example
>
> Input Box > "Hello"
>
> I would like it to then search column A1:A20 for the Word 
> Hello. Once found
> just leave the cell selected and a message box saying the word 
> has been found.
>
> If anyone could do this for me I would be very greatful I have 
> tried now for
> 2 days and finding it really tough.
>
> Thanks for the help 


0
chip1 (1821)
4/28/2006 11:50:03 PM
Thanks Chip however I found a solution and modified it to what I wanted its 
pretty cool. Seperated into two different modules. Thanks for your time and 
the reply.

Chris

"Chip Pearson" wrote:

> Chris,
> 
> Try some code like the following:
> 
> Dim FoundCell As Range
> Dim WhatToFind As String
> WhatToFind = InputBox("Find What?")
> If WhatToFind = "" Then
>     Exit Sub
> End If
> Set FoundCell = Range("A1:A20").Find(what:=WhatToFind, _
>     LookIn:=xlValues, lookat:=xlWhole)
> If Not FoundCell Is Nothing Then
>     FoundCell.Select
>     MsgBox WhatToFind & " found at " & FoundCell.Address
> End If
> 
> 
> -- 
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
> 
> 
> "Chris" <Chris@discussions.microsoft.com> wrote in message 
> news:EADBBF89-80DC-491A-AE6F-58ED91C4DC22@microsoft.com...
> > Hello I am new to Excel VBA and think I have thrown myself into 
> > the deep end
> > by trying to produce an advanced spreadsheet.
> >
> > What I am trying to do is have a button on my spreadsheet that 
> > when clicked
> > opens up an Input Box, the user can input some data and then it 
> > searches a
> > column for the matching input.
> >
> > For Example
> >
> > Input Box > "Hello"
> >
> > I would like it to then search column A1:A20 for the Word 
> > Hello. Once found
> > just leave the cell selected and a message box saying the word 
> > has been found.
> >
> > If anyone could do this for me I would be very greatful I have 
> > tried now for
> > 2 days and finding it really tough.
> >
> > Thanks for the help 
> 
> 
> 
0
Chris6982 (632)
4/29/2006 12:02:12 AM
Reply:

Similar Artilces:

check box #3
I want to put some check boxes on my worksheet. When the boxes are checked I want them to be totaled at the end of the column. I inserted the check boxes but when I put the formulas for the sum of the columns I get an error message. I am guessing that I have to make a positive check box = the value of one. Don't have a clue on how to do it. Any help would be appreciated. Dennis You can set up a linked cell for each of the checkboxes. The way you do that depends on the type of checkbox you used. If you used a checkbox from the Forms toolbar, rightclick on it and choose Forma...

Getting Data Shapes to Work with Custom Stencils
I imported a picture and saved it as a shape. Then I linked data from a SQL database. I can get the data to show up in the Shape Date, but I can't get a Data Graphic to appear. Note that I can get Data Graphics to work with out of the box shapes in the same drawing - just not the custom picture. Tried in both Visio 2007 and 2010 Beta. What am I doing wrong? Thanks in advance. ...

Combine multiple rows into one row with multiple columns
Hi, I have a table set up so that there are three columns: StudyID, DrawDate, and Value. StudyID and DrawDate are the primary key. I want to create a table from this one that has only one row for each StudyID so that it would go from: StudyID DrawDate Value to StudyID DrawDate1 Value1 DrawDate2 Value2 DrawDate3 Value3 etc. Is there a way to do this? Thanks, Elysia "Elysia Larson" <elysia.larson@gmail.com> wrote in message news:832e952f-174f-489f-ab7a-e2189f660a92@f6g2000vbp.googlegroups.com... > Hi, > > I have a...

Column to Rows
I want to convert my data from one column into rows. I have my data set up now as follows: John Smith $3200 555 Main St. 95111 Jane Jones $5500 345 Happy Dr. 93434 Jack Clark $2300 354 Oak Pl. 95343 I want it to be displayed into 4 separate columns as follows: John Smith $3200 555 Main St. 95111 Jane Jones $5500 345 Happy Dr. 93434 Jack Clark $2300 354 Oak Pl. 95343 Please advise, thanks, Don -- Don D. ------------------------------------------------------------------------ Don D.'s Profile: http://www...

Charts switch from 'Series in Rows' to 'Series in Columns'
I use VBA to create charts in Excel 2003, but find that sometimes the Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not intended), even if I have specified 'Series in Rows'. This happens intermittently, and I am not sure what I am doing wrong. I do save the workbook as Microsoft Excel 97 so that a user with Excel 2000 or Excel 2003 can use the workbook. Thank you for any suggestions. Hard to tell if you keep the code secret. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Peac...

How to Arrange Data for Chart
I charted the following data in a clustered column chart. Level 1 Level 2 Level 3 Level 4 FY07 - Total 167 23 20 43 81 FY08 - Total 178 22 40 26 90 These are total department errors segregated by levels for each fiscal year. There are 3 different departments involved. What I did was just total the errors for each FY and each level. Now my boss wants to see how many errors were performed within each level by Dept A, Dept B and Dept C and compare the 2 fiscal years. I hope I’m explaining...

Right column spilling onto 2nd page
I bought a template from a vendor and saved it as a new template with my version of excel. The link to that file is: http://www.utahhousevalues.com/lgfiles.cfm. The vendor is not able to help me figure out why the right column is spilling onto a second page. There are 49 total pages and about 25 of them spill the right column onto a second page. They claim each page is identical and it should not do that. I'm using Small Business version of Excel 2000 with XP Professional SP-1 THANKS MUCH Darrell Catmull http://www.utahhousevalues.com Hi Darrell, If the vendor can't tell you w...

Creating a Campaign Response via Follow-up
When you create a new Task/Phone Call or other activity via the Follow-up tab, the new Activity is associated with the entity you are looking at. (so following up a Contact with a Phone Call will create a Phone Call which is associated with the Contact) This is not the case for the Campaign Response activity - when you create a Campaign Response via the Follow-up tab, the Customer field (ie regarding) is left blank and hence not associated with anyone. Presumably this is a bug - anyone know if there is a hotfix for this? Kind Regards, Paul. ...

How to update contacts in WLM via csv file?
I have nearly 2,000 email addresses in my contacts list. I managed to get WLM to list them, although I do notice some are missing. Now, I want to update them with new/revised contacts through a new cvs file, which I exported from OE. However, the contacts import program just hangs and doesn't take. Do I need to delete the existing list first? If so, how do I do that? I am using WLM v.2009 Build 14.0.8 on Windows 7 Are you logging in to Live services? It will say your profile name under the close X. -- Ron Sommer "Bumper21248" wrote in message news...

Replace Cells with Column names in functions?
I have a # of fairly long/complex cell functions that get hard to debug because there are also a lot of rows. Is there anyway to change display so it replaces the column name e.g. If(BT1204="X". BA="Y" to If(CustomerName="X", CustomerCode="Y") ? CustomerName is a defined name range for BT1204 Find & Replace Find what: BT1204 Replace with: CustomerName "msnyc07" wrote: > I have a # of fairly long/complex cell functions that get hard to debug > because there are also a lot of rows. > > Is t...

Users and Asset Data linked on Form
Hello everyone, I have two tables, one a Staff list and the other an Asset list extracted from seperate databases, the information in the tables has absolutely no common data, I have a form with the user name and other bits and pieces on from the staff list and also a drop down list of all asset numbers which I can then select the appropiate asset number from then, that asset number and other details I would like to be associated to that user. I have tried, I have done this in the past about 4 years ago but I cannot remember any of it! Any help would most appreciated Cheers David David, T...

How do I change the text size in a drop down box
I am using excel 2003. It seems to default to 10pts. Changing the size in the source list or the default in the general tab did not change the size. ...

How do I put Excel data into a US map format?
I want to feed Excel data about population and trends into a map format instead of a bar graph or pie chart. Is there a plug-in or some such thing that I can use that works with Excel? Ultimately, I want to have each state depicted by a color code for a range of population or an amount of certain data. I am using Excel 2002 in a Windows XP environment. ...

Problem with Text box and Background
I am using the background as a temple for a form I add test boxes where I want to have text typed in The problem is when I print the page the text I have typed in isn't where it is supposed to be It is below the line of the background form How do I solve this problem? Web page? Best asked in the publisher.webdesign group -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "News" <gmhull@sbcglobal.net> wrote in message news:pOKFf.27487$F_3.19174@newssvr29.news.prodigy.net... >I am using the background as a t...

text boxes that label axes in line/scatter graphs
I tried to use "SAT-M" as y-axis label, but "M" was cut off (actually went to second line that could not be seen). I tried every trick I know to lengthen the text box, to fit label on one line. Hi, The only way to length chart textboxes is with more text. But at some point more text will cause the content to wrap. The charts textboxes do not have the ability to be sized manually even though the bounding box and grab handles would suggest otherwise. Try adding a few spaces or even non-breaking spaces ALT+0160 (Numpad) Cheers Andy Peg wrote: > I tried to use &qu...

how do I sum only visible data in a column
I have some rows manually hidden, Please is there a formula I can apply to return the sum of the unhidden data contain in a column? Hi Afolabi, If you don't want to use VBA (create a UDF), you could review the following pages: http://www.jkp-ads.com/Articles/ExcelNames08.htm http://www.jkp-ads.com/Articles/ExcelNames09.htm In any case, there is a fundamental issue with both approaches: since hiding/unhiding rows doesn't trigger any event, such event will have to be forced from time to time, or maybe you can leave with the formulae updating their result in the next recalculation...

Can Import Email Data Filed from Office XP to Office 2003
My HD failed but I had a backup of my Office XP Pro personal file folder with a lot of emails I needed. The file is about 128MB in size. I put a new HD in and installed Office 2003 Pro. When I go to open the old file Outlook 2003 says that the file "is not a personal folders file". (It was backed up to a CD ROM prior to the crash so the source file should be good.) I really don't want to uninstall Office 2003 and put back Office XP. Any idea why 2003 won't open the file and how to work around that? Many thanks in advance to anyone who knows the solution. Alan. The only pro...

Importing External Data
I keep trying to import data from an Access database and I continually get the same error. "ODBC Microsoft Access Driver The text file specification "Inserts" does not exist You cannot import., export or link using this specification." Well the spec does exist, the queries run just fine in Access. The data source are linked text files using the "Inserts" specification, which definitely does exist. Sumpens wrong, but danged if I know what it is. Any ideas? TIA Paul Hammond ...

Adding up numbers from columns.
How can I sum up multiple columns while not letting each cell "associated values" sum to a number greater than one specified. For example, if I have sets of colums: 14 3 12 2 11 2 11 1 10 1 and 20 5 19 4 18 3 17 2 and 25 6 22 4 20 3 18 2 I want to know what 3 combination of numbers in the left 3 colums giv me the highest value...WITHOUT letting the correspoding numbers in th right hand column be greater than, say 10. Thanks, Larr -- Message posted from http://www.ExcelForum.com ...

Data Validation date field
I don't understand why this isn't working. I just want to make sure that they don't put a future date in the polydate field. Is me.polydate > now() an invalid expression? code: _________________________________________________________ Private Sub Form_BeforeInsert(Cancel As Integer) If Me.PolyDate > Now() Then Cancel = True Me.PolyDate.SetFocus MsgBox "Please enter a date that falls prior to today's date" Exit Sub End If End Sub It does not appear to be invalid. It is always helpful to post the error you are getting and if it is a runti...

How to Update the Data in RichEditView
Hi every body, I am trying to split the RichEditView,its splitting,but i am not able to update the data in all views.If any body knows give a suggestion. Regards, Subbaiah. ...

Result of one combo box, affecting results of another...
Just out of curiousity... Is there a way to have what the user selects in one combo box on a form, affect what is available for the user on another box!? For instance... Box A has 8 different addresses.. Within those addresses there are 12 different building ID locations... Instead of having all 12 building locations for each address always appear in the combo box, If the user picks building 4, is there a way to just have the building ID options for building 4 showing in combo box B? Charles! Use your favorite search engine. Use "Cascading Combo boxes" for search terms. -- Reg...

Transpose Address Data
Is there any way to transpose a range of data automatically? I know about using the transpose function in Excel, but to use it I need to manually select what I want to transpose. I have about 13000 rows that I want to transpose (end result would be about 1000 rows of address details). The biggest problem I have is that I can't find any thing in the list of data that highlights where I would want to start a new range. It also looks like not everything is a set number of rows, so I can't even transpose every 10 rows. Is there anything I can do other than select, copy, paste speci...

Sum a table of columns & rows
I have a spreadsheet of 154 Rows (all unique project numbers in numerical order) and 9 columns of account numbers (some are similiar and some are user entered, therefore there could be 'blanks' with no data in them). I am trying to create a table that will only give me the project number if there are dollars in one or more of the columns. This would be used for data entry (and that is why I would like to have the columns summed up - to remove duplicates). Any ideas? I have given a brief example below: F, G, &am...

Multiple yes/no boxes
I have a table that tracks steps completed in a project. The steps are setup as yes/no boxes. I am trying to write a querry the will bring back the current step in progress. For example, there are boxes for pursuit, purchase, construction, close, etc. When I run the report, I want only the last checked field to display. So if there are checks in pursuit, purchase and construction. I would like the report to display construction. Thank you ...