bluesheep
06-19-2014, 11:24 AM
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 :) :) :)
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 :) :) :)