Can u set this up in vba excel Using I5 as your anchor cell (which means you must use offset from I5 for all the questio
Posted: Tue Jul 12, 2022 8:28 am
Can u set this up in vba excel
Using I5 as your anchor cell (which means you must use offset from I5 for all the questions below and make use of the With..end With construct). Finding the number of rows of items you have might be helpful in this case.
(i) insert a title "Subtotal" in a cell of column J that is two rows below the last entry in that column (i.e. there must be a gap of 2 rows).
(ii) insert a title "Discount" in the cell below the cell in which you wrote "Subtotal"
(iii) insert a title "Total" in the cell below the cell in which you wrote "Discount"
(iv) in column K, in the cells below "Total Cost", calculate the Cost of Units using the following formula in R1C1 format: Cost of Units = # of Units * Price per Unit The price per unit must be picked from the data in columns C,D and E based on the number of units of each type the customer wishes to buy.
(You can use a simple if‐else statement for this and no need to introduce loops. At this point, we are not bothered about any new type of machine the user enters and is concerned only about calculating for the 3 given machines, so do not worry about making the code very generic.)
(v) Format the values calculated in (iv) above such that it shows atleast one digit to the left of the decimal and always shows two digits to the right of the decimal point.
(vi) The cell to the right of "Subtotal" must show the sum of the values in the cells below "Total Cost". Use formula in R1C1 format. Format this value such that it has a $ sign and shows atleast one digit to the left of the decimal and always shows two digits to the right of the decimal point. Also, store this value to a double variable called dblSubtotal. (Hint: Read the 2nd tip given at the end of the problem.)
(vii) The cell to the right of "Discount" must display the value in cell E2 (absolute reference) if thesubtotal value is greater than $65,000, else it must show zero. Store this value to a double variable called dblDiscount. Format this value such that it shows in percentage when displayed on your sheet with atleast one digit each on both sides of the decimal (but don't format when you store it to the double variable).
(viii) The cell to the right of "Total" must calculate and display the value of the formula:
Total = (1 ‐ Discount) * Subtotal
You can make use of the double variables defined above in this formula. Format this value such that it has a $ sign and shows atleast one digit to the left of the decimal and always shows two digits to the right of the decimal point.
Using I5 as your anchor cell (which means you must use offset from I5 for all the questions below and make use of the With..end With construct). Finding the number of rows of items you have might be helpful in this case.
(i) insert a title "Subtotal" in a cell of column J that is two rows below the last entry in that column (i.e. there must be a gap of 2 rows).
(ii) insert a title "Discount" in the cell below the cell in which you wrote "Subtotal"
(iii) insert a title "Total" in the cell below the cell in which you wrote "Discount"
(iv) in column K, in the cells below "Total Cost", calculate the Cost of Units using the following formula in R1C1 format: Cost of Units = # of Units * Price per Unit The price per unit must be picked from the data in columns C,D and E based on the number of units of each type the customer wishes to buy.
(You can use a simple if‐else statement for this and no need to introduce loops. At this point, we are not bothered about any new type of machine the user enters and is concerned only about calculating for the 3 given machines, so do not worry about making the code very generic.)
(v) Format the values calculated in (iv) above such that it shows atleast one digit to the left of the decimal and always shows two digits to the right of the decimal point.
(vi) The cell to the right of "Subtotal" must show the sum of the values in the cells below "Total Cost". Use formula in R1C1 format. Format this value such that it has a $ sign and shows atleast one digit to the left of the decimal and always shows two digits to the right of the decimal point. Also, store this value to a double variable called dblSubtotal. (Hint: Read the 2nd tip given at the end of the problem.)
(vii) The cell to the right of "Discount" must display the value in cell E2 (absolute reference) if thesubtotal value is greater than $65,000, else it must show zero. Store this value to a double variable called dblDiscount. Format this value such that it shows in percentage when displayed on your sheet with atleast one digit each on both sides of the decimal (but don't format when you store it to the double variable).
(viii) The cell to the right of "Total" must calculate and display the value of the formula:
Total = (1 ‐ Discount) * Subtotal
You can make use of the double variables defined above in this formula. Format this value such that it has a $ sign and shows atleast one digit to the left of the decimal and always shows two digits to the right of the decimal point.