Find a word in excel [Solved/Closed]

- - Latest reply: TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
- May 2, 2011 at 09:11 AM
Hello,

I have set up a spreadsheet to keep track of my daily workouts. The spread sheet is set up as follows:

Column A: Date
Column B: Description of 1st workout
Column C: workout 1 score
Column D: Additional work
Column E: Description of 2nd workout
Column F: workout 2 score

with each month being on a new worksheet.


Each workout can range from 1 to 10 different exercises. I am wanting to write a macro that will enable me to search for all workouts that contain a particular (or combination of) movements. For example if todays workout involved pull ups, box jumps and squats I want to be able to search back through all previous workouts that include squats or all previous workouts that include the combination of both pull ups and squats and have those days entire row copied to new worksheet so i can look over my scores of similar workouts and track my progress.


I would like to search columns B, D & E (in numerous worksheets) for particular words (e.g push up) and have the entire row in which they appear to be copied to a new worksheet.


I have no experience in writing macros and any help would be greatly appreciated.


Thanks, Tim



See more 

2 replies

Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
0
Thank you
Hi Tim,

It would be really helpfull if you could upload your workbook using a site like www.speedyshare.com.

I was thinking about an inputbox which would let you input and seacrh for a workout. Put the results in a summary sheet. Then you could search for another workout to be placed in the summary sheet for you to look for combinations of workouts. Then you can manually clear the summary sheet to look for other workouts.

Does this sound ok?

Best regards,
Trowa
0
Thank you
Hi Trowa,

yes that sound good, i have uploaded the excel file, it can be found at:
http://www.speedyshare.com/files/28062410/Crossfit_Workouts_2011.xlsx

Thank you for your help!!!

Tim
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383 -
Hi Tim,

Sorry, forgot to tell that I'm working with excel 2003. Could you resave/reupload your workbook with a .xls extention.

Thank you,
Trowa
http://www.speedyshare.com/files/28079393/Crossfit_Workouts_2011.xls
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383 -
Hi Tim,

Hopefully you are not in any rush of getting the answer. Since you use merged cells I have got to use the Find method, which I never used before. I like figuring it out but it will take some time. When I get stuck I will let you know as well as asking an expert to look at your query.

Best regards,
Trowa
yeah no worries, thanks for your help

Tim
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383 -
Hi Tim,

Here is your file with code:
http://www.speedyshare.com/files/28254792/Crossfit_Workouts_2011.xls

Please note that when merging cells, the value will be put in the top most cell.
Since you used four cells to merge the date and three cells to merge your excercises, I had to unmerge your date to put in on the same row as your exercises.
I have done this for the April sheet and two times for the May sheet for testing purposes.
You will need to do this for the rest of the sheets as well for the code to work properly.

On the Summary sheet I used the bold font to highlight what you searched for. When clearing the search results the bold font needs to be cleared as well. To make this happen in one step I have created a button for your convenience.

Also look at the format of the summary sheet to make date look like date and time look like time etc..

The code in its current state will only work for April and May. So look at the code and add the other sheets as well.

Have you thought about how/when to run the code? Using a button or activate when selecting a sheet.

Can you work with this?

Best regards,
Trowa