PDA

View Full Version : VBA Question - How to find the contents of one cell in another worksheet?



shuffleslipp
06-14-2008, 07:48 AM
Sheet 1: User enters his name and clicks on a search button.
Sheet 2: Contains a name list in Column A and information about user's age, weight, and height in Columns B, C, and D, respectively. The rest of the columns show the team calendar using colors to indicate vacation days and work days.

Once a user enters his name and clicks on search in Sheet 1, the Macro should search for his name in Sheet 2 (the Master sheet with all info). Upon finding his name, the Macro should copy that entire row of information and paste it into an empty row in Sheet 1 so that the user can view and update his information. It needs to paste the entire row because I need all cell formatting to be included. If the Macro can't find the name entered, a message box should appear indicating so.

After the user updates his information in the Sheet 1, I want another Macro to update this info back into Sheet 2.

The macro that I recorded keeps finding the contents of the original name I used when recording the macro. I need it to be dynamic so that it'll search the new name every time.

I have attached a sample spreadsheet with modified names and no recorded macros. Any help is appreciated. Thanks!
-Bruce

Simon Lloyd
06-14-2008, 10:13 AM
Just record your actions using the macro recorder using Excels Find!

shuffleslipp
06-14-2008, 10:25 AM
Macro recorder doesn't read new entries dynamically. It keeps giving me the same row when I search.

Simon Lloyd
06-14-2008, 02:21 PM
Of course it does!, you need to modify it so that it looks at the cell you are typing your search criteria in so change the What:=????? to be something like Range("A1").Value