--- VBA ( visual basic application) PROGRAM --- Sub Prob6_2() ' Place a control button in the worksheet SalesData ' Your code should work with
--- VBA ( visual basic application) PROGRAM ---
Sub Prob6_2() ' Place a control button in the worksheet "SalesData" ' Your code should work with data sheets with different number of regions and/or ' different number of months '1. Find out the number of regions '2. Find out the number of months '3. Format the header so they are horizontally centered, have blue background, ' Italic, and Black text. '4. Add the title name "Total Sales" two columns after the last region; ' Center the text horizontally and make the background yellow. '5. Use R1C1 formula to calculate total sales for each month, put them under ' "Total Sales" column ' '6. Moving average of total sales; Ask size of moving average from user until the user ' gives a positive numeric answer '7. Create moving avearge column ' Clear the contents and formats in the column that is three columns to the right ' of the last column with the sales data ' Put column heading "Moving Average (#)" where # is the the number of months specified ' by the user from step 6; format the header with left-justified horizontal alignment, ' blue, bold, and italic text. ' Calculate moving average numbers, the first one should (#+1)th row below the heading ' the last one should be one row below the last record. Show these numbers with only ' two digits to the right of the decimal '8. Calculate Average for each region and place them two rows below the last row of data. ' showing no digits after decimal point ' Write the title "Average" in column A using offset from the anchor cell. ' Format the header so it is horizontally centered, have yellow background, ' Italic, and Blue text End Sub
Before sheet ...
After sheet...
ElVBAProject (Hw6-2 19a (1).xlsm) Microsoft Excel Objects ThisWorkbook Sub HW6 2 ) Place a control button in the worksheet "SalesData" Your code should work with data sheets with different number of regions and/or 'different number of months 1. Find out the number of regions 2. Find out the number of months 3. Format the header so they are horizontally centered, have blue background, wsData (SalesData After) wsData1 (SalesData) Modules t Module 1 Italic, and Black text. Center the text horizontally and make the background yellow "Total Sales" col umn 4. Add the titie name "Total Sales" two columns after the last region: 5.Use Rici formula to calculate total sales for each month, put them under 6. Moving average of total sales: Ask size of moving average from user until the user gives a positive numeric answer Clear the contents and formats in the column that is three columns to the right Put column heading "Moving Average (#)" where # is the the number of months specified 7. Create moving avearge column of the last column with the sales data by the user from step 6; format the header with left-justified horizontal alignment, blue, bold, and italic text. Calculate moving average numbers, the first one should (#+1) th row below the heading the last one should be one row below the last record. Show these numbers with only two digits to the right of the decimal 8 Calculate Average for each region and place them two rows below the last row of data showing no digits after decimal point Write the title "Average" in column A using offset from the anchor cell. Format the header so it is horizontally centered, have yellow background, Italic, and Blue text roperties Module1 End Sub odule1 Module 3348 3679 5 2 1 867713 931230 31 72337 14 6333-34 2 2 99039 34314 4 2 115 4535 237 1247 6865 791 la 2 1 hn b-r-r-y-n-g-p tae n-b-r-r-y-n-g-p uue uue 1-2, 450709 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 123 45-6789 Sales Data Month Total Sales Moving Average ( 4 1351 1218 1694 1652 1301 1264 1073 1512 1752 1664 1657 1359 1801 383 617 873 636 574 1432 1467 1092 1096 1355 978 958 5671038 1397 938 1519 1233 263 474 5918 Feb-12 1137 1227 1170 1696 1113 1501 1378 1601 1381 1188 1158 1598 1636 1317 1327 1331 1103 1371 1414 5270 809 5209 4914 5763 6606 5307.43 6754 5405.71 54515646.43 57925672.29 54315784.14 4710 5815.86 55865786.71 6705 5761.43 58845775.57 5316 5651.29 7187 5632.00 5170 5831.29 5335 5794.00 5605 5883.29 945 1674 Sep-12 Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 1712 1373 1164 276 595 1480 1024 1418 1661 889 477 510 1210 1158 1332 1432 1399 1178 1495 1058 1272 1023 552 1329 1337 1052 1708 1596 1535 1856 992 524 897 5886.00 Average 1487 626 1201 1365 974 ElVBAProject (Hw6-2 19a (1).xlsm) Microsoft Excel Objects ThisWorkbook Sub HW6 2 ) Place a control button in the worksheet "SalesData" Your code should work with data sheets with different number of regions and/or 'different number of months 1. Find out the number of regions 2. Find out the number of months 3. Format the header so they are horizontally centered, have blue background, wsData (SalesData After) wsData1 (SalesData) Modules t Module 1 Italic, and Black text. Center the text horizontally and make the background yellow "Total Sales" col umn 4. Add the titie name "Total Sales" two columns after the last region: 5.Use Rici formula to calculate total sales for each month, put them under 6. Moving average of total sales: Ask size of moving average from user until the user gives a positive numeric answer Clear the contents and formats in the column that is three columns to the right Put column heading "Moving Average (#)" where # is the the number of months specified 7. Create moving avearge column of the last column with the sales data by the user from step 6; format the header with left-justified horizontal alignment, blue, bold, and italic text. Calculate moving average numbers, the first one should (#+1) th row below the heading the last one should be one row below the last record. Show these numbers with only two digits to the right of the decimal 8 Calculate Average for each region and place them two rows below the last row of data showing no digits after decimal point Write the title "Average" in column A using offset from the anchor cell. Format the header so it is horizontally centered, have yellow background, Italic, and Blue text roperties Module1 End Sub odule1 Module 3348 3679 5 2 1 867713 931230 31 72337 14 6333-34 2 2 99039 34314 4 2 115 4535 237 1247 6865 791 la 2 1 hn b-r-r-y-n-g-p tae n-b-r-r-y-n-g-p uue uue 1-2, 450709 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 123 45-6789 Sales Data Month Total Sales Moving Average ( 4 1351 1218 1694 1652 1301 1264 1073 1512 1752 1664 1657 1359 1801 383 617 873 636 574 1432 1467 1092 1096 1355 978 958 5671038 1397 938 1519 1233 263 474 5918 Feb-12 1137 1227 1170 1696 1113 1501 1378 1601 1381 1188 1158 1598 1636 1317 1327 1331 1103 1371 1414 5270 809 5209 4914 5763 6606 5307.43 6754 5405.71 54515646.43 57925672.29 54315784.14 4710 5815.86 55865786.71 6705 5761.43 58845775.57 5316 5651.29 7187 5632.00 5170 5831.29 5335 5794.00 5605 5883.29 945 1674 Sep-12 Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 1712 1373 1164 276 595 1480 1024 1418 1661 889 477 510 1210 1158 1332 1432 1399 1178 1495 1058 1272 1023 552 1329 1337 1052 1708 1596 1535 1856 992 524 897 5886.00 Average 1487 626 1201 1365 974
