1

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 screen of the code

Example of table now 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

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Guy Leggett
  • 11
  • 3
  • Please show us your work sor far and your exact table structure - screenshots would be very helpful. – Michal Rosa Nov 20 '18 at 04:47
  • Please always include the code in your question and format it as code block. [An image of your code is not helpful](http://idownvotedbecau.se/imageofcode). – Pᴇʜ Nov 20 '18 at 07:27
  • 1
    You will find [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) very helpful in terms of speeding up your code by avoiding .Select. – QHarr Nov 20 '18 at 07:51

1 Answers1

0

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73