I got the answer for the coding but I need someone to explain the coding which i
ID: 3870595 • Letter: I
Question
I got the answer for the coding but I need someone to explain the coding which is which and what is what
Question 2:
Create a User Defined Function that accepts an unknown range of numbers and determines if a pattern of either 6 consecutive numbers trending up or 6 consecutive numbers trending down exists anywhere within the range. Beware, the range provided may have more than 6 numbers in it. The input range will be a one dimensional range as shown in the example below. How you display to the user that an upward/downward trend exists is up to you.
5
2
57
67
89
90
100
Trend Up
1
5
8
9
5
4
8
No Trend
100
99
98
87
65
55
74
Trend Down
Function counTrend(bunchOfCells As Range)
Dim numCols As Integer
Dim startCol As Integer
Dim endCol As Integer
Dim currentValue As Integer
Dim upcounter As Integer
Dim downcounter As Integer
Dim upward As Boolean
Dim downward As Boolean
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
upward = False
downward = False
upcounter = 0
downcounter = 0
numCols = bunchOfCells.Columns.Count
startCol = bunchOfCells.Column
i = bunchOfCells.Row
endCol = startCol + numCols - 2
For j = startCol To endCol
currentValue = Cells(i, j).Value
l = Cells(i, j + 1).Value
If currentValue < l Then
upcounter = upcounter + 1
downcounter = 0
Else
downcounter = downcounter + 1
upcounter = 0
End If
If upcounter >= 5 Then
upward = True
ElseIf downcounter >= 5 Then
downward = True
End If
Next j
If downward = False And upward = False Then
MsgBox ("No trend.")
ElseIf downward = True Then
MsgBox ("Trend Down.")
Else
MsgBox ("Trend up.")
End If
End Function
5
2
57
67
89
90
100
Trend Up
1
5
8
9
5
4
8
No Trend
100
99
98
87
65
55
74
Trend Down
Explanation / Answer
Commenting each line for better understanding:
Function counTrend(bunchOfCells As Range) // declaration of countTrend function
// Declaration of variables
Dim numCols As Integer
Dim startCol As Integer
Dim endCol As Integer
Dim currentValue As Integer
Dim upcounter As Integer
Dim downcounter As Integer
Dim upward As Boolean
Dim downward As Boolean
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
upward = False //making value of Boolean variable as false by default
downward = False //making value of Boolean variable as false by default
upcounter = 0 //making value of integer variable as 0 by default
downcounter = 0 //making value of integer variable as 0 by default
numCols = bunchOfCells.Columns.Count // counting the number of numbers entered
startCol = bunchOfCells.Column // holding the value of the column number of a cell
i = bunchOfCells.Row // holding the value of the row number of a cell
endCol = startCol + numCols – 2 // finding out the end column number of the entire grid
For j = startCol To endCol // iterating from start column number to the end column number that is moving left to right in each row and then going to the next row when the row end Is reached.
currentValue = Cells(i, j).Value // extracting the current value of a cell.
l = Cells(i, j + 1).Value // extracting the current value of the next cell.
If currentValue < l Then // comparing the value of the current cell and its next cell
upcounter = upcounter + 1 // increasing the counter if next cell value is greater then the current cell value
downcounter = 0 // initializing the downcounter as 0 if current value of a cell is less then the value of the next cell.
Else
downcounter = downcounter + 1 // increasing the counter if next cell value is smaller then the current cell value
upcounter = 0 // initializing the downcounter as 0 if current value of a cell is greater then the value of the next cell.
End If
If upcounter >= 5 Then // checking upcounter value
upward = True // if upcounter value greater than 5 then initialize Boolean variable upward to true
ElseIf downcounter >= 5 Then // checking downcounter value
downward = True // if downcounter value greater than 5 then initialize Boolean variable upward to true
End If
Next j
If downward = False And upward = False Then //According to the downward or upward value displaying the messege box.
MsgBox ("No trend.")
ElseIf downward = True Then
MsgBox ("Trend Down.")
Else
MsgBox ("Trend up.")
End If
End Function
Basically, the loop is iterating from the left to right row wise and when it reaches the row end its goes to the next row and iterates again, along the way comparing the current cell value to the immediate next cell value.
I have solved your question. Please do not forget to give a positive like to the answer. Thank you.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.