New to VBA! Code for a sales calculator, how to output tables to user interface?

[bluesheep]

Hi,
So I started learning VBA yesterday and dont quite know how to do this.
Some info:
I am writing some code for an excel sales product interface, where the user is shown different buttons for different products. When they press the button they are prompted to input several values for pricing of the product. I have taken that information and stored it in separate excel sheets for the different products. Those sheets do some calculations with the input and then on the same sheet, it reports major values that should be shown to the user. Each product has maybe 5 or 6 values to report to the user.

My issue is that I do not know how to add additional lines of additional products after the user clicks calculate. For example, if I have 5 lines of output on a spread sheet, I use this to report it to the main interface.

Worksheets("UI Calculator").Range("S4").Value = Worksheets("ERP").Range("F18").Value
Worksheets("UI Calculator").Range("S5").Value = Worksheets("ERP").Range("F19").Value
Worksheets("UI Calculator").Range("S6").Value = Worksheets("ERP").Range("F20").Value
Worksheets("UI Calculator").Range("S7").Value = Worksheets("ERP").Range("F23").Value
Worksheets("UI Calculator").Range("S8").Value = Worksheets("ERP").Range("F24").Value
Worksheets("UI Calculator").Range("S9").Value = Worksheets("ERP").Range("F26").Value
Worksheets("UI Calculator").Range("T4").Value = Worksheets("ERP").Range("G18").Value
Worksheets("UI Calculator").Range("T5").Value = Worksheets("ERP").Range("G19").Value
Worksheets("UI Calculator").Range("T6").Value = Worksheets("ERP").Range("G20").Value
Worksheets("UI Calculator").Range("T7").Value = Worksheets("ERP").Range("G23").Value
Worksheets("UI Calculator").Range("T8").Value = Worksheets("ERP").Range("G24").Value
Worksheets("UI Calculator").Range("T9").Value = Worksheets("ERP").Range("G26").Value


this creates a 6x2 Table for one product. Lets say I want to prompt the user to add another product, by adding another button "addNewProduct". How can I have a Nx2 table that will add product information to it if there is an empty row in those columns (or it can even be an empty row after a certain place.) So it doesn't matter if there is (ex 4x2, 5x2, 6x2 etc) information, it will keep adding rows with whatever additional products are requested with whatever dimension (ex display 15x2 table on main user interface). The manual approach to each cell works okay with one product at a time because I can just clear that box, but I feel there is a faster and more efficient way.
Thanks so much guys :) :) :)

[Bob Phillips]

Maybe something like


With Worksheets("ERP")

For i = 18 To .Cells(.Rows.Count, "F").End(xlUp).Row

Worksheets("UI Calculator").Cells(i - 14, "S").Value = .Cells(i, "F").Value
Worksheets("UI Calculator").Cells(i - 14, "T").Value = .Cells(i, "G").Value
Next i
End With

[bluesheep]

Thanks xld, but that seems to be the opposite of what I want to do (but this will be very helpful with input). I know what the dimensions for the different spread sheets, those dont change. My question is on the main spreadsheet where the user gets all of the info, how do I make it add on. For simplicity lets say I have 5 columns datasets with 1,2,3,4,5 data values respectively (a,b,c,d,e). Each is a different spreadsheet. How can I call them at different times to the main spreadsheet and have them stack properly in a main column.
ex calling spreadsheet 1 and spreadsheet 3:
a
a
b
c
ex calling spreadsheet 4 and spreadsheet 2 and spreadsheet1:
a
b
c
d
a
b
a
ex calling spreadsheet 5 and 3
a
b
c
d
e
a
b
c
I think I can get a command for the individual spreadsheets to report the values to the main spreadsheet column, but how can I add a button to give the user to add another option and the results from the chosen spreadsheet calculations will follow the previous calculation in the results column.

[westconn1]

if you want to leave one empty row between products, find the last row on target sheet and add 2
try like

nextrow = Worksheets("UI Calculator").cells(Worksheets("UI Calculator").rows.count, "s").end(xlup).row + 2
Worksheets("UI Calculator").Range("S" & nextrow).Value = Worksheets("ERP").Range("F18").Value
'etc

you can also reduce your 6 lines of code to 2 by copying the 2 contiguous ranges
like

Worksheets("UI Calculator").Range("S4:s6").Value = Worksheets("ERP").Range("F18:f20").Value
Worksheets("UI Calculator").Range("S7:s9").Value = Worksheets("ERP").Range("F23:f26").Value

both these examples can be combined if they are helpful

[bluesheep]

THANK YOU! I am sorry to ask but I have one more question. So I tried messing around with the top two code. and when I tried stuff like:
it worked for like those lines and then ran a #N/A error for the next 70 lines. So what would the code be to transfer more than just F18. I mean im fine manually doing it for each cell, but kind of wanted to know how to do it for the S column and T column, multiple rows at a time.
nextrow = Worksheets("UI Calculator").cells(Worksheets("UI Calculator").rows.count, "s").end(xlup).row + 2
Worksheets("UI Calculator").Range("S4:S7" & nextrow).Value = Worksheets("ERP").Range("F18:F20").Value

[westconn1]

then ran a #N/A error for the next 70 linespost a workbook with some sample data for testing, include a sheet with desired result