Find data by entering data in another cell

[Solved/Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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.

4 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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.
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

VenKat,

Please forgive me you received a duplicate of this message but it appears it did not post correctly in the Forum.

I want to thank you so very much for the code - it works great! There is one other thing I would like to do in reference to this same issue. When the VBA code takes the user from Cell A10 in the MainMenu sheet to the corresponding ID number in Column C of Sheet 6, is there a way to add a line of code to actually select the name of the project which is in Column B. So in my original example - when the user types the number 331 into Cell A10 on the MainMenu sheet and the code brings him to Cell B4 on sheet 6 (which is the name of the project with the ID number 331 in Cell C4). I would then like to make Cell B4 temporarily fill with a Yellow color so it is easy to find in the list of multiple projects on Sheet 6.

Again, thank you for such great code. You have saved me countless hours of work.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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
Thank you so very much - AGAIN! This works great!
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?
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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)