In: Computer Science
In VBA, write a code that does as follows: The first worksheet ("Ex. 1") has a list of 50 numbers. Write a program that will read them into an array, then will calculate and output the following: - How many of the numbers are even (output in E2) - How many of the numbers are greater than 300 (output in E3) - The average of the numbers (output in E4) - In column B, output next to each number, the difference between that number and the average (can be positive or negative)
In case of any query, do comment. Please rate answer. Thanks
Please use below code in your module:
Sub Numbers()
'
' Numbers Macro
'
'
'to select the worksheet
Application.Workbooks("Numbers.xlsm").Worksheets("Ex.1").Select
Dim numberArray As Variant
'read cell range from 2 to 51 in an array
numberArray = Application.Transpose(Range("A2:A51"))
Dim average As Integer
Dim sum As Integer
Dim evenCount As Integer
Dim numberGreaterThan300 As Integer
'iterate over array
For i = 1 To 50
sum = sum + numberArray(i) 'add to sum
If numberArray(i) > 300 Then
'if number is greater than 300 then increase numberGreaterThan300 count
numberGreaterThan300 = numberGreaterThan300 + 1
End If
If numberArray(i) Mod 2 = 0 Then
'if number is even, increase even count
evenCount = evenCount + 1
End If
Next
average = sum / 50 'find the average
Cells(2, 5).Value = evenCount 'put even count in E2
Cells(3, 5).Value = numberGreaterThan300 'put numbers count greater than 300 in E3
Cells(4, 5).Value = average 'put average in E4
For j = 2 To 51
' substract average from A column and put it in B
Cells(j, 2) = Cells(j, 1) - average
Next
End Sub
====================screen shot of the code=========================
Output: