Question

check 1&2

do 3-5 in VBA

BAProject (C13-sale Data s Micosoft Excel Objects 1) Caleulate the sum of sales tor January, put the result in B17 Players (Players) Din colunnsum As Integer colunnau0 For i-1 To 13 wSales(Sales) s Modules n columnsum +ActiveWorkbook.Morksheets("Salea").Range ("84:816*).Cellsj. 1).Value Next BAProject (HomeworksWorksheets("Sales").Range "B17")columnsum Microsolft Excel Objects Players (Players) 旬nsales (Sales) Modules 2) Calculate the sum of sales for Febuary, put the result in c1 Module colusnsum- For j-1 To 13 colunnsum -columnsum ActiveWorkbook.Work sbeets(Salea") Range(C4C1).cella. 1. Valse Next Horksheets("Sales").Range(C17")colunnsum 3) Caleulate the sum of sales for the rest of months with nested for loop, and ' put them in the correspoinding place For į_3To 9 colunnsun- For j _ 1 To 13 colunnsum-columnsum Act i veNorkbook. worksheets("Sales") Range ("B4:316) .Cells(3. i).Value Next Worksheets("Sales") Range("D17:317-) Cells(j) -columum ' 4) Find how many players from Miani Heat are in the list ' 5) Find hou many point guards from Atlantic division are in the 1ist Homework5 Your,Name (1)- Excel HelpTell me what you want to do File Home InsertDraw Page Layout Formulas Data Review View M26 1 Sales by region and month Jan-15 Feb-15 Mar-15 Apr-15 May-15 Jun-15 2270 1290 1600 2100 1170 1920 1110 2060 3130 16650 5 Region 2 6 Region 3 1730 3150 1180 740 160 9001830 1220 2090 1700 1390 1720 880 16750 2160 1380 18530 3380 1850 19520 940 17730 1910 2580 3470 2220 2200 22010 2000 1270 2090 2110 2040 2270 1650 1910 Region 6 1820 25702060 2190 1840 10 Region 7 11 Region 8 Region 9 2400 1880 2980 2370 1680 1680 3120 1010 1550 2230 2960 2240 2120 2040 2800 2290 1520 17650 1620 19510 2650 18590 770 2740 18690 2080 2910 1900 15 Region 12 17601590 1610 1550 1730 110 3660 1670 3440 18160 16 Region 13 1870 1330 19302080 26440 27460 3360 1930 1100 17660 T15 1 First Name 2 Kobe Last Name Salary Position Team Division Pacific Atlantic Atlantic 27849149 SG LA Lakers NY Knicks NY Knicks LA Lakers LA Lakers Miami Heat Miami Heat Miami Heat Oklahoma Thunder Chicago Chicago Bulls San Antonio Spurs Oklahoma Thunder NY Knicks Chicago Bulls San Antonio Spurs Chicago Bulls San Antonio Spurs San Antonio Spurs LA Lakers Oklahoma Thunder LA Lakers 20463024 SF Stoudemire 19948799 PF 19536360 с Gasol 19000000 PF 17545000 Southeast 17545000 SG 17182000 PG 16669630 SF 16402552 PG 8 LeBron Southeast Northwest Central Central Southeast Northwest 10 Kevin Boozer 15000000 C Ginobili Chandler Parker 14107492 SG 13668750 PG 13604188 с 13305000 SF Central Southeast 12500000 PG Noah 11300000 C 18 Joakinm Southeast Southeast 10059750 SF 9638554 C 8900000 PG 8300531 C 7258960 SF 5820417 SG 5800000 PF Duncan 21 Steve Northwest Pacific Perkins 24 James 25 Mike 26 Richard 27 Marcus Miami Heat Southeast 5000000 SG Atlantic 4590338 C 4500000 PF 4060000 PF 4054055 SF NY Knicks San Antonio Spurs Miami Heat NY Knicks LA Lakers Miami Heat San Antonio Spurs Southeast Southeast Diaw Pacific 30 Steve 4000000 PG Chalmers Southeast 32 Mario 3944000C 3941000 PG Home Insert Draw Page Layout Formulas Dats Review Vi Help T Tell me what you want to de Hinrich Anthony Bonner Sefolosha 3941000 PG 3750000 C 3630000 PF 3600000 SG 3563600 PF 3500000 PG Chicago Bulls Miami Heat San Antonio Spurs Southeast Oklahoma Thunder Northwest LA Lakers LA Lakers San Antonio Spurs NY Knicks Miami Heat Oklahoma Thunder Northwest Miami Heat NY Knicks Oklahoma Thunder Northwest NY Knicks Oklahoma Thunder Northwest Oklahoma Thunder Northwest Oklahoma Thunder Northwest Chicago Bulls Chicago Bulls San Antonio SpursSoutheast NY Knicks LA Lakers Miami Heat 36 Matt Pacific Duhon 3480453 PG 3135000 SF 3090942 PG 3090000 SG Raymond Southeast Battier Cook 43 Daequan Southeast Atlantic 45 Jason 46 Nick 2929332 PF 2806452 SG 2445480C 2338721 PG 2253062 C 2155811 P 1957000 SG 1809840 SF 1633440 SG 1500000 SG 1500000 S 1400000 PG 1352181 PF 1352181 C 1240000 PF 1208400 SG 1200000 C 1174080 SF 1085120 PCG 1082520 PG 1074720 SG 1066920 SG Collison Smith Aldrich Atlantic 49 Eric 50 Serge lbaka Central 52 Marco 53 Kawhi 54 Iman Leonard Meeks DeColo Atlantic Pacific Southeast 56 James 57 Nando 58 Rashard Miami Heat NY Knicks Southeast Atlantic Clark Jackson Thabeet Oklahoma Thunder Northwest Oklahoma Thunder Northwest Oklahoma Thunder Northwest San Antonio Spurs Southeast Miami Heat San Antonio SpursSoutheast Butler HomeworkS, Your, Name (3) Exce File Home Insert DrawPage Layout Formulas DataReviewView Help Tell me what you want to do 64 Patty 65 Norris 66 Cory 67 Jimmy 68 Devin 1085120 PG 1082520 PG 1074720 SG 1066920 SG 1054389 PF 1054000 C 962195 SG 857000 SG 854389 C 854389 PF 854389 PG 854389 P 854389 P 854389 C 854389 SG 854389 SG 854389 SF 762195 PF 473604 SF 473604 PCG San Antonio Spurs Southeast Southeast Southeast Central Pacific Southeast Joseph Butler Ebanks San Antonio Spurs Chicago Bulls LA Lakers San Antonio Spurs LA Lakers Chicago Bulls Chicago Bulls Chicago Bulls Chicago Bulls LA Lakers Miami Heat Miami Heat Miami Heat 70 Darius 71 Marcus Morris Central Central Central 73 Vladimir 74 Nate 75 Antawn 76 Rodney 77 Dexter 78 Garrett 79 Ronnie 80 James 81 Josh 82 Chris 83 Pablo Mohammed Radmanovic Robinson Jamison Carney Pittman Temple Brewer White Harrellson Copeland Prigioni Pacific Southeast Southeast Southeast Atlantic NY Knicks Miami Heat NY Knicks NY Knicks Atlantic 94

Answer 1

Use below VBA code SUB for the 3 to 5 actions:

3) Calculate the sum of sales for rest of the months, this code will calculate sum for all columns from B till K and display in 17th row

Sub SumSales()

Dim columnsum As Integer

For i = 1 To 10
columnsum = 0
For j = 1 To 13

columnsum = columnsum + ActiveWorkbook.Worksheets("Sales").Range("B4:K16").Cells(j, i).Value
Next
Worksheets("Sales").Range("B17:K17").Cells(i) = columnsum
Next
End Sub

4) How many players from Miami heat, this code will apply filter in active sheet on column E for Miami Heat and display msgbox of count of players

Sub MiamiHeat()
  
With ActiveSheet
Set rnData = .UsedRange
With rnData
.AutoFilter Field:=5, Criteria1:="Miami Heat"
.Select
For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
lcount = lcount + rngarea.Rows.Count
Next
MsgBox "Autofilter " & lcount - 1 & " records"
End With
End With

End Sub

5) How many PointGaurds form atlantic division, this code will apply 2 filter criteria on active sheet on column F as Atlantic and column D as PG and display count of rows in messagebox

Sub AtlanticPG()
  
With ActiveSheet
Set rnData = .UsedRange
With rnData
.AutoFilter Field:=6, Criteria1:="Atlantic"
.AutoFilter Field:=4, Criteria1:="PG"
.Select
For Each rngarea In .SpecialCells(xlCellTypeVisible).Areas
lcount = lcount + rngarea.Rows.Count
Next
MsgBox "Autofilter " & lcount - 1 & " records"
End With
End With

End Sub