Option Explicit

Sub HW6_2()

' 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

Region 1 Region 2 Region 3 Region 4 Region 5 Total Sales Moving Average (7) 1519 5918 5069 5270 5009 5209 4914 5763 6606 5307.43 67545405.71 5451 5646.43 5792 5672.29 54315784.14 4710 5815.86 5586 5786.71 6705 5761.43 5884 5775.57 5316 5651.29 71875632.00 5170 5831.29 5335 5794.00 5605 5883.29 4 1351 1218 1694 1652 1301 1264 1073 1512 1752 1432 1467 1092 1096 1355 978 958 1038 1397 Feb-12 Mar-12 Apr-12 1137 1227 1170 1696 574 1674 1988 1712 Jul-12 1501 1378 1601 1381 1188 1158 1598 1636 1317 1327 1331 1103 1371 Oct-12 1657 1359 1801 1509 1329 1337 1052 1708 1596 1535 1856 477 1373 1210 1158 1332 1432 1399 1178 1495 1058 1272 1023 Dec-12 1164 Feb-13 Mar-13 Apr-13 1480 1024 1661 524 897 5886.00 Average 1201 1365 SalesData SalesData After Region 1 Region 2 Region 3 Region 4 Region 5 Total Sales Moving Average (7) 1519 5918 5069 5270 5009 5209 4914 5763 6606 5307.43 67545405.71 5451 5646.43 5792 5672.29 54315784.14 4710 5815.86 5586 5786.71 6705 5761.43 5884 5775.57 5316 5651.29 71875632.00 5170 5831.29 5335 5794.00 5605 5883.29 4 1351 1218 1694 1652 1301 1264 1073 1512 1752 1432 1467 1092 1096 1355 978 958 1038 1397 Feb-12 Mar-12 Apr-12 1137 1227 1170 1696 574 1674 1988 1712 Jul-12 1501 1378 1601 1381 1188 1158 1598 1636 1317 1327 1331 1103 1371 Oct-12 1657 1359 1801 1509 1329 1337 1052 1708 1596 1535 1856 477 1373 1210 1158 1332 1432 1399 1178 1495 1058 1272 1023 Dec-12 1164 Feb-13 Mar-13 Apr-13 1480 1024 1661 524 897 5886.00 Average 1201 1365 SalesData SalesData After