Need Help Figuring Out Practice Code In Language VBA (Visual Basics For Applicat

Question

Need Help Figuring Out Practice Code In Language VBA (Visual Basics For Applications in Excel)

The data is included in this table:

YOU SHOULD PASTE THE TABLE IN EXCEL FROM ROWS C5 TO F5 AND COLUMNS FROM C5 TO C55

The table includes 3 tests for students.

a) Use code name wsEx assign cell C5 to the variable rngA Use cell C5 as a reference cell to do the following and use With construction:

b) Format headers with red font, horizontal center, italics (start from C5 and use offset and end method to find the range for headers. Don’t just use A1 address such as Range(“C5:F5”))

c) Calculate the average for each Test grade and put them on the second empty row below the grades(start from C5 and use offset to specify the range for saving average and use R1C1 reference style for formula. FormulaR1C1 will be "=Average(R[#]C:R[#]C)")

d) The standard deviation for each Test grade and put them on the next row

e) The minimum grades and put them below the standard deviation

f) The maximum grades and put them below the minimum

g) On the first column with data, label each of the four rows added appropriately (e.g., Average, StdDev, Min, Max)

h) Calculate the average over the 3 Test grades and put them on the second empty column to the right of the last column with Grades, Label the column "Average" (average over 3 test: FormulaR1C1 will be “=Average(RC[#]:RC[#])”)

i) Sort the data from largest to smallest based on grade in Test 1

ID Test 1 Test 2 Test 3 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 100 84 80 78 71 85 66 91 81 84 95 62 51 74 62 55 69 88 61 59 52 79 89 66 62 88 62 94 55 94 80 52 54 87 65 51 97 65 100 63 93 78 58 54 73 96 50 71 59 55 72 77 95 58 65 62 81 98 74 62 72 75 64 59 50 62 90 77 95 92 87 78 54 60 84 87 94 96 78 72 83 64 85 96 52 73 67 77 74 58 92 59 81 62 56 99 90 75 67 81 68 88 93 83 88 56 83 63 83 100 91 55 70 83 62 85 52 50 75 61 74 68 96 64 52 94 65 74 99 84 87 65 61 65 71 91 80 89 70 88 94 86 65 96 86 91 61 68 67 84

Explanation / Answer

Hi, the code is given below. I've used absolute references for the sorting part of the question as using the Selection and range variables did not seem to work for some reason. You can change the code for sorting if you wish.

Just a little tip for programming in Excel VBA:- whenever you are stuck just quickly record a macro doing part of whatever task you want done. Excel will save this code as a new Sub. You then just need to look at that code, make appropriate changes and paste it in your own macro.

The code is as follows:-

Sub weEx()
Dim rngA As Range

'Question a)
Set rngA = Range("C5") 'save a "starting point" for your macro in a range variable rngA

'Question b)
rngA.Select
Range(Selection, Selection.End(xlToRight)).Select 'select all continuously populated cells in the row from rngA
With Selection
.Font.Color = vbRed
.HorizontalAlignment = xlCenter
.Font.Italic = True
End With

'Question c)
rngA.Select
Selection.End(xlDown).Select 'jump to the last continuously popullated cell in this column
ActiveCell.Offset(2, 1).Select 'now select the cell that is next column to the right and two rows below(2nd empty row below the grades)
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-51]C:R[-2]C)" 'calculate average
'next column
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-51]C:R[-2]C)" 'calculate average
'next column
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-51]C:R[-2]C)" 'calculate average

'Question d)
ActiveCell.Offset(1, -2).Select 'jump to the cell in the next row two columns behind below test1 scores)
ActiveCell.FormulaR1C1 = "=STDEV(R[-52]C:R[-3]C)" 'calculate std deviation
'next column
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=STDEV(R[-52]C:R[-3]C)" 'calculate std deviation
'next column
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=STDEV(R[-52]C:R[-3]C)" 'calculate std deviation

'Question e)
ActiveCell.Offset(1, -2).Select 'jump to the cell in the next row two columns behind below test1 scores)
ActiveCell.FormulaR1C1 = "=MIN(R[-53]C:R[-4]C)" 'calculate minimum
'next column
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MIN(R[-53]C:R[-4]C)" 'calculate minimum
'next column
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MIN(R[-53]C:R[-4]C)" 'calculate minimum

'Question f)
ActiveCell.Offset(1, -2).Select 'jump to the cell in the next row two columns behind below test1 scores)
ActiveCell.FormulaR1C1 = "=MAX(R[-53]C:R[-4]C)" 'calculate minimum
'next column
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MAX(R[-53]C:R[-4]C)" 'calculate minimum
'next column
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MAX(R[-53]C:R[-4]C)" 'calculate minimum

'Question g)
ActiveCell.Offset(-3, -3).Select 'jump to the cell three columns behind and three rows up from the current cell
'this should be the cell to the left of the cell with Test1 average score
ActiveCell = "Average"
ActiveCell.Offset(1, 0).Select 'go to the next row
ActiveCell = "StdDev"
ActiveCell.Offset(1, 0).Select 'go to the next row
ActiveCell = "Min"
ActiveCell.Offset(1, 0).Select 'go to the next row
ActiveCell = "Max"

'Question h)
rngA.Select 'select the first cell of the data
Selection.End(xlToRight).Select 'jump to the last continuously popullated cell in this row
ActiveCell.Offset(0, 1).Select 'select the cell in the next column
ActiveCell = "Average"
ActiveCell.Offset(1, 0).Select 'go to the next row
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-3]:RC[-1])" 'calculate average
Selection.Copy 'copy this cell
'select all cells in which this formula is to be pasted
ActiveCell.Offset(0, -1).Select 'first select the cell to the left
Selection.End(xlDown).Select 'jump to the last continuously populated cell in this column This will be the column with Test3
ActiveCell.Offset(0, 1).Select 'go back to the column on the right (The averages column you have just created with the average score for this question
Range(Selection, Selection.End(xlUp)).Select 'select all cells in this column that the average formula needs to be pasted into
'Range("G7:G10").Select
'Range("G10").Activate
ActiveSheet.Paste 'paste the formula in all these selected cells

'Question i)
'using absolute references as Selection and range objects dont seem to be working with sort for some reason

'please make changes to this part if required
Range("D6:I55").Sort Key1:=Range("D6:D55"), Order1:=xlAscending


End Sub

Hope this helps!

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.