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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnHB" wrote in message
...
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

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" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnHB" wrote in message
...
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

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
Microsoft MVP Excel
SalesAid Software

"JohnHB" wrote in message
...
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" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnHB" wrote in message
...
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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
Microsoft MVP Excel
SalesAid Software

"JohnHB" wrote in message
...
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" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnHB" wrote in message
...
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