Agfdhyk

First off, I've tried to do my research, but I'm so inexperienced I don't know what I don't know, and I don't know how to ask the right question. I'd imagine my whole issue is just not understanding the functionality of excel.

I'll start by explaining my story and goals - I'm open to trying a more efficient method!

TL;DR - I need to create some sort of column search function that offsets a value

My Story:

So, I am about to launch an online personal training business. One of my services is nutrition coaching, and I'll be creating weekly meal plans for clients. To create these meal plans I want to be able to create a 'preset meal' button that I can easily press to add a given preset to the meal plan. I've been operating inefficiently, where I manually enter the quantity of each ingredient each time. I have tables for the different food groups (meat, grains, dairy, fruit, veg, other), they will be set up like the following. In this example, one of the required ingredients is 2 slices of ham (see Column F);

This is the previously set up macro, it was selecting a given Cell, not finding the ingredient and inputing a value from an offset

Example of table now

Column A - Ingredient - e.g;

Meat Table;'pork_ham_slice'

Column B - Energy (kj) - e.g;

Meat Table;'460'

Column C:E - Carbs(g)|Protein(g)|Fat(g) - e.g;

Meat Table;'7'|'15'|'2.5'

Column F - Quantity - e.g; -

Meat Table;'2'

Column G - Energy (kj)x Column F - e.g;

Meat Table;'920'

Column H:J - [Carbs(g)|Protein(g)|Fat(g)]x Column F - e.g;

Meat Table;'14'|'30'|'5'

My Goal: I want to be able to create a button/macro that will search Column A for the given ingredient, before inputing a value into Column F. I had started creating a few buttons where they input values into a specific Cell in Column A, however, I will continually be adding more ingredients, and I want the Column A to be sorted alphabetically, which instantly throws off this set cell method as I add a new ingredient.

At the end of the day, I thought some combination of VLOOKUP and OFFSET could be used, but I just can't figure this one out - Youtube isn't enough, and here we are.

If any of this was unclear, I'm more than happy to provide further details!

Guy

To give you an example how to find a specific value in a table:

Dim FoundIngredient As Range

Set FoundIngredient = Worksheets("MeatTable").Range("A:A").Find(What:="Pork_Ham_Slice", LookAt:=xlWhole)



If FoundIngredient Is Nothing Then

    MsgBox "ingredient not found"

    Exit Sub

Else

    FoundIngredient.Offset(ColumnOffset:=5).Value = 2

End If