Find data by entering data in another cellSolved/Closed

Question

Hello,

I have an Excel 2007 Workbook that I use for tracking the status of over 50 projects.  This Workbook contains 6 worksheets plus a "MainMenu" page where I have created buttons with Macros so a user can jump to any page without using the sheet tabs.  On page 6 there is a summary of all the projects.  Each project has a unique project number under a column title of "ID"

What I want to do is establish a cell on the "MainMenu" page that a user can enter the project number of a particular project and either a Macro or VB code will activate when the information is entered and immediately take the user to the particular cell location for that project on sheet 6.  As an example:  I have the following information in Cells C2:C6 (with C1 being the column name "ID"
205
804
331
925
632

On Sheet 1 or "MainMenu", I want to designate Cell A10 as the activation cell.  I want the user to be able to enter any of the above numbers into Cell A10 and as soon as the "enter key" is hit, the user will be brought to the appropriate cell.  Example, he types the number 331 into Cell A10 on the MainMenu sheet and the code will bring him to Cell C4 on sheet 6.  I would also like to highlight this cell when the system lands on it.

Thanking you all in advance.

venkat1926 · Accepted Answer

right click main menu sheet tab and click view code and in the resulting window paste this event code 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cfind As Range
On Error Resume Next
If Target.Address <> "$A$10" Then Exit Sub
With Worksheets("sheet6")
.with Columns("c:c")
Set cfind = .Cells.Find(what:=Target.Value, lookat:=xlWhole)
End With
If Not cfind Is Nothing Then
Worksheets("sheet6").Select
cfind.Select
Else
MsgBox "this ID is not avialble"
End If
End Sub

now type anly number in A10 of the main menu sheet see what happens.

note the IDs are in sheet called "sheet6" and in column C.

if there is any problem post the statement where the error occurs and what is the error message.

venkat1926 · Answer

slightly modified macro 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cfind As Range
On Error Resume Next
If Target.Address <> "$A$10" Then Exit Sub
With Worksheets("sheet6")
.With Columns("c:c")
Set cfind = .Cells.Find(what:=Target.Value, lookat:=xlWhole)
End With
If Not cfind Is Nothing Then
Worksheets("sheet6").Select
ActiveSheet.Cells.Interior.ColorIndex = xlNone
cfind.Select
cfind.Interior.ColorIndex = 6
Else
MsgBox "this ID is not avialble"
End If
End Sub

bari · Answer

if enter the contains of project, instead of Ware house, i need to search either ware house, or Ware or house.., how can i get the result?

venkat1926 · Answer

not clear . upload extract file with sheets Main and sheet 6  to speedyshare.com and give the uploaded web pae for downloading and also explain with example(s)

Find data by entering data in another cell

4 responses

Similar discussions

Subscribe To Our Newsletter!