Meal Planner - Edit Macro Code

[Pwhite84]

Hi There,

I'm relatively new to Excel and even newer to this forum. I have downloaded a Family Meal Planner that is full of Macro's. Is there anyone that would be willing to take a look at the code and let me know what I need to change? If so can I send an email or do I just post it up here? I'm pretty sure its very simple just don't know what I'm doing. I want to:-

• Change the Date Format
• Change the period for compiling shopping list from 1 week to one month.
• Add an additional column on the main sheet that is then taken into shopping calculations

If anyone would be willing to help I would be very very grateful.

Thanks,

Pete

[Richard Buttrey]

Hi, and welcome to the forum. Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Pwhite84]

Hi Richard,

Thanks for your help. I think I have now changed it please could you check this now complies or advise what would be more appropriate? Thanks, Pete

[Richard Buttrey]

Hi Pete,

You'll need to upload the workbook here and tell us what you are wanting to do with it step by step. i.e. we need to understand both the tasks that should happen and also the order in which they take place.

You say it already contains macros but unless we know what YOU want to happen we can't tell whether the current macros will do that.

[Pwhite84]

Ok No problem....

Here Goes. I'll start with a summary of each Tab.

Meal Plan includes three meals and snacks per date. Serving Column has no real use from what I can work out so can be ignored

Ingredients each line is given a recipe (which becomes its Tag or reference) a measurement, Qty and description which when it is selected should compile into the shopping list.

Recipe Tab is where a meal is given a name, a serving size and page reference. I believe this is the table that then drops into meal plan.

Shopping this is where the list made up of the previous tabs should compile.

The sheet works fine at the moment but I can't work out how to change the following things:-

• Date into english format with Day
• I want to be able to do a shopping list calculation between a variable amount of days rather than the 7 it is currently locked into.
• I want the "snack" column on the Meal Plan Tab to reference the same way that the breakfast, lunch and dinner columns do.

Idea being I enter a recipe name 1st, a list of ingredients 2nd, I plant the meal to a certain date in the future and then go to the shopping tab - enter a date range I want to produce a list for and press compile list and get an accurate list based on all of the previous information.

Hope that makes sense and hope someone can help.

Thanks,

[Richard Buttrey]

Hi,

See if the attached does what you want.

I've added H4 on the 'shopping' tab for you to enter the end date instead of the default + 7

[Pwhite84]

Hi Richard,

The new box certainly makes a lot of difference so thanks for that. There are still a couple of anomalies in the code....

Between the 21/04/2014 and the 31/05/2014 Roast Chicken appears three times. according to the recipe I need 1 whole chicken per meal, however the shopping list says that I only require 1 for the whole period? Similar think with Chicken which is currently set to every day for lunch and also Spicy indian pollock appears twice yet the shopping list only asks for one egg yolk. I haven't checked everything but guess this could be consistent throughout?

Also it still appears to be ignoring the snack column in its entirety from the first sheet.

I have attached the file incase it is something I have done.

Thanks so much for your help with this. It will be great to get this up and running. Is there a limit to how many meals i can put in both the main sheet and the recipe builder?

[Richard Buttrey]

Hi,

Other than addressing the three points you asked about I didn't review the rest of the macro code since I didn't, and still don't know precisely what you want this system to do. I had rather assumed you had found a working system and just wanted some minor mods.

If you can give a full specification of what you want to happen with reference to the original data and the process control that you want to take place then maybe someone will be able to help further. Often with these things it's easier to work out code from scratch rather than get into the mind of someone who's written code in their own way. Particularly when the overall aim is not known.

[Pwhite84]

Hi Richard,

Thanks, to be honest it does everything I want. Its just that last bit that doesn't quite tally it still appears to be looking at less than the range set in the date boxes. I take your point though and thanks for your help today.

Could you point me in the right direction for learning how to write my own Macros?

Thanks,

Pete