One Entry Cell area, to add items to lists

[JohnHB]

So Basically I have a several lists of foods and points:

Meats Points
Fruits_Vegetables Points

Bacon Bits, Hormel 50% less fat (1 Tbsp) 0.63 Apple, medium 0.80
Bacon, HEB Fully Cooked (4 slices) 1.98 Artichoke Hearts (3) 0.20

Currently when a user needs to add a new food item they go to the point
calculator, calculate points, then go to the appropriate list, and add the
food at the bottom of the list with its points. Then hit a sort marcro tab
at the top of the list to put the foods in alphabetical order. Then those
foods are available to them in a drop down list that is in another area of
the spreadsheet.

I was trying to think of a way, to have a user select what area of food they
are entering Meats, Fruits & Veggies etc. (I can do this easily with a data
valadation list), then enter the name of the new food, and then using the
points calculator calculate the points. So three cells of info: Type of
food, food name, points

My struggle is how do I get a list to save the value at the bottom of the
Type of Food list, so that the same three cells can change and the newest
item be saved in the list still. Stop referencing those cells once the new
item is added?

Just thought it would be easier if there was just one area to input the
information instead of the user memorizing what the points were, then adding
the new food name and points to the bottom of the list then having to hit
sort.

Is there an easier way to do that all in one area?

Thanks,
JohnHB

 

[Don Guillett]

This line of code identifies the next available row in col A

lastrow=cells(rows.count,"a").end(xlup).row+1

 

[JohnHB]

Hi Don,

Thanks for the quick reply, I going to more help then that though, I am
experienced with Excel but not as much with Visual Basic and programming,
most macros I create are by recording them. Most my programing is done in
cells with if statements etc.

Thanks,
John

 

[Don Guillett]

I gathered, from what you said, that you already had a macro and only wanted
to find the last row. Perhaps you should post all of your code for comments.
If all else fails, send your file to my address below with a snippet of this
msg and clear explanations with before/after examples.

 

[Don Guillett]

Sub PutFoodInCategoriesAndSort()
what = Range("o8").Value
mc = Rows("4").Find(what, After:=Cells(4, 1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Column
lrmc = Cells(Rows.Count, mc).End(xlUp).Row + 1

Cells(lrmc, mc) = Range("p8")
Cells(lrmc, mc + 1) = Range("q8")
Range(Cells(6, mc), Cells(lrmc, mc + 1)).Sort Key1:=Cells(6, mc),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub