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

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

CodePudding user response：

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/

CodePudding user response：

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