Originally Posted by
Anshu
Point 2)..... I think, the point got unnoticed. It's here...
… All of the above conditions are applied if there are minimum 3 consecutive cells which are in descending order.
For example, cells D5, E5 and F5 have values which are satisfied both the condition, i.e, they are in descending order, and they are consecutive (side by side)…
Yes , I think I missed that.
But its quite easy to do. As previously there are probably lots of ways to do it because
_ there are lots of logics to achieve the same
_ for each logic there are usually many different ways to apply it in VBA
The following macro change is the first thing I thought of, so it is unlikely to be the best
We change the inner loop that is looping through the columns,
Code:
Dim Clm As Long ' "column" in table array
For Clm = 2 To UBound(arrTbl(), 2) - 1 ' loop from second to last but one "column" in table array
If arrTbl(Cnt, Clm + 1) >= arrTbl(Cnt, Clm) Then
Exit For ' we no longer have a decresing sequence
Else
End If
Next Clm
If Clm = UBound(arrTbl(), 2) Then ' this will occur if we did not exit the For loop
This alternative below will keep looping along the columns until we either
have 3 consecutive cells which are in descending order,
or until we have checked all columns
Code:
Dim Clm As Long: Let Clm = 2 ' "column" in table array
Dim Decs As Long
'For Clm = 2 To UBound(arrTbl(), 2) - 1 ' loop from second to last but one "column" in table array
Do
If arrTbl(Cnt, Clm + 1) >= arrTbl(Cnt, Clm) Then ' we no longer have a decresing sequence
Let Decs = 0 ' Reset the count of sequential decreasing values
Else ' we have at least 2 sequential decreases, possibly 3
Let Decs = Decs + 1
End If
'Next Clm
Let Clm = Clm + 1
Loop While Clm < UBound(arrTbl(), 2) And Decs < 2
'If Clm = UBound(arrTbl(), 2) Then ' this will occur if we did not exit the For loop
If Decs = 2 Then ' If decs = 2 we had three seqeuntial decreses = sequentially 2 x arrTbl(Cnt, Clm + 1) < arrTbl(Cnt, Clm)
Dim StrRemmark As String
Let StrRemmark = StrRemmark & " and " & arrTbl(Cnt, 1)
'Let StrRemmark = StrRemmark & ", " & arrTbl(Cnt, 1)
Else
End If
Let Decs = 0 ' reset the count of sequential decreasing values so that Decs can be used in the next main row loop
Here is a full macro including that inner loop change: https://excelfox.com/forum/showthrea...ll=1#post14915
Point 1) Missing comma : When all the three rows contains values in descending order, then B4 shows - Student is decreasing in ENGLISH and HINDI and MATHS
It should be - Student is decreasing in ENGLISH, HINDI and MATHS (as we normally write in English language)
In VBA there are lots of useful sting manipulation functions and statements. ( I guess this all comes from the earlier Visual Basic ( VB ) language, and/or the fact that all software is just long strings of text, so string manipulation is fundamental to any computer software…. I would advise learning about the various string functions at an early stage of learning VBA https://docs.microsoft.com/en-us/dot...ring-functions )
Once again , we can think of many different ways to change the final string format
My first thought is to replace the
“ and “
With
“, “
Then , if we have at least one “, “ in the final string, then we will replace that with “ and “
There is an extra change that you may be interested in: We can easily change something like
MATHS
to
Maths
One way to do that is to replace a code line like
arrTbl(Cnt, 1)
to
Left(arrTbl(Cnt, 1), 1) & Mid(LCase(arrTbl(Cnt, 1)), 2)
That change effectively changes a string like
MATHS
To
M & aths = Maths
Here is the next macro version: https://excelfox.com/forum/showthrea...ll=1#post14916
That event macro is also in the first worksheet of the uploaded file ( Share link below )
Alan
Share link 'Dynamic Table.xls' : https://app.box.com/s/366xsar7yt28pfsserl84mopfczoyow9
Reply With Quote
Bookmarks