How to register an order in a line below the last line with data?

Question

I have a form that I need to present the request in a summarized form on the right side, as shown in the image. I currently have the following code. enter image description here

I would like to click on the icon for the request to appear in column N in the 1st line without data. Because if the order is only for 1 water, the order will currently appear on the 2nd line (because I had defined it and I don't want it)

Sub menu()

Range("N6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[13]C[-12]"
Range("Q6").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(Meal_register!RC[-3],Prices_Table!R[-2]C[-14]:R[6]C[-13],2,0)"
Range("Q7").Select
End Sub

Sub water()

Range("N7:O7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[12]C[-9]"
Range("Q7").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-3],Prices_Table!R[-3]C[-14]:R[5]C[-13],2,0)"
Range("Q8").Select
End Sub

What's your question? What does "Register an order" mean? Can you explain with lots more details what you are trying to do and where you are stuck. — JNevill, Mar 21 '22 at 18:49

anderson · Answer 1 · 2022-03-21T19:52:13.283

2

You can use an auxiliary variable to know what is the row of the last item on the list.

Dim aux As Integer
aux = Range("N5").End(xlDown).Row

with this, you can use something like cells(aux+1,"column you need") and write the next item.

#complete

Sub menu()
aux = Range("N5").End(xlDown).Row
cells(aux+1,14)="Menu"
cells(aux+1,17).select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(Meal_register!RC[-3],Prices_Table!R[-2]C[-14]:R[6]C[-13],2,0)"
End Sub


Sub water()

aux = Range("N5").End(xlDown).Row    
cells(aux+1,14)="Water"    
cells(aux+1,17).select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-3],Prices_Table!R[-3]C[-14]:R[5]C[-13],2,0)"
End Sub

maybe would exist an error with the vlookup function. Try to make something more general with worksheetfunction. Read the following link: https://www.exceltrick.com/formulas_macros/vlookup-in-vba/

edited Mar 21 '22 at 19:52

answered Mar 21 '22 at 19:18

anderson

31
2

In the code I have above, how did I frame the code you gave me? @anderson – Beatriz Mar 21 '22 at 19:26
I complete my answer. I hope it helps you – anderson Mar 21 '22 at 19:53
1

Except, [use `Long`, not `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long), right? – BigBen Mar 21 '22 at 19:59
1

Please also note that `End(xlDown)` is generally regarded as the wrong way to find the last row - [here's](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) the canonical for the proper way. – BigBen Mar 21 '22 at 19:59
I'd advise generally against declaring row variables as integers. 65536 (2^16) is/was the maximum number of rows for Excel 97-2003. Therefore potential row counts greater than 65k in newer versions (up to 2^20=1048576 rows) will exceed the limit defined by an Integer datatype. @anderson – T.M. Mar 21 '22 at 20:43
Thank you @anderson! Can you check my other question at link: https://stackoverflow.com/questions/71574151/how-can-i-record-conditional-data-in-a-list-with-vba-code – Beatriz Mar 22 '22 at 14:56
@BigBen, we're not looking for the last row. We are looking for the first blank cell below N5. The presence of data below the area where we need to place the data prevents us from looking up. – Gove Mar 22 '22 at 17:27
@Gove - one can look up, if one starts in cell N21, which is pretty much what OP ended up doing (pun intended haha). – BigBen Mar 22 '22 at 17:28
@BigBen - That's true, and for that reason, I think Marya's own answer is very good (+1). However, in this case, if there is a blank cell below N5 that has a filled cell below it, as might be the case if an item had been removed, we would favor filling in the blank cell over adding another to the end of the list. – Gove Mar 22 '22 at 17:33

score 2 · Answer 2 · answered Mar 22 '22 at 14:09

With your help, I managed to find a super simple code:

Sub menu()
linha = Range("N20").End(xlUp).row + 1
Cells(linha, 14) = ("Menu")
 
End Sub

Sub water()
linha = Range("N20").End(xlUp).row + 1
Cells(linha, 14) = ("Water")
End Sub

In the values of each icon, I chose to use a vlookup manually. Thanks for your help!!

score 1 · Answer 3 · answered Mar 21 '22 at 19:27

It seems like in the "water" sub procedure, you want to get the first empty cell after N5 to put the entry for water. You'll want something similar for your other items that can be ordered. If so, here's what I recommend. Usually, we would serach up from the bottom of the sheet to find the last row with data, then offset by one to get the first empty cell. However, your "total" line prevents that approach. So do this instead.

Put a space character in N4. Actually, any text would work, but a space won't change the visual appeal of your beautiful sheet.

change sub Water as follows:

 Sub water()
     Dim row As Integer
     row = Range("n4").End(xlDown).row + 1
     Range("N" & row & ":O" & row).Select
     Application.CutCopyMode = False
     ActiveCell.FormulaR1C1 = "=R[12]C[-9]"
     Range("Q" & row).Select
     ActiveCell.FormulaR1C1 = _
         "=VLOOKUP(RC[-3],Prices_Table!R[-3]C[-14]:R[5]C[-13],2,0)"
     Range("Q" & (row + 1)).Select
 End Sub

Oh, I see that this solution is similar to anderson's. The example I provide will work even when you are adding the first item. — Gove, Mar 21 '22 at 19:30
Thank you! Can you check my other question at link: https://stackoverflow.com/questions/71574151/how-can-i-record-conditional-data-in-a-list-with-vba-code — Beatriz, Mar 22 '22 at 14:53

How to register an order in a line below the last line with data?

3 Answers3

Linked