Showing Custom Value Based on the Condition of Dynamic Table

[Anshu]

Hello!

I have a table B4:G4. (simplified here, actual table is long enough having more than 80 cells)
3416

B4, B5 and B6 are linked to another cell. So their value may change depending on the value of the linked cell. (therefore, I used the term 'dynamic table')

There is a merged cell range A1:C1, where the remarks would be appear.


Now, my requirement is...

1. If value of C4 > D4 > E4 > F4 > G4, (descending order), then the merged cell A1 will show the value " Student is decreasing in (Value of Cell B4)"
For example, "Student is decreasing in English" ( Here the the cell value "English" may change, so I used "(Value of Cell B4, instead of the current value, which is English)

2. Similarly, If value of C6 > D6 > E6 > F6 > G6, then the merged cell A1 will show the value " Student is decreasing in (Value of Cell B6)"
For example, "Student is decreasing in Maths"

3. Similarly, If value of C5 > D5 > E5 > F5 > G5, then the merged cell A1 will show the value " Student is decreasing in (Value of Cell B5)"
For example, "Student is decreasing in Hindi"

(It can be noticed that points 1, 2 and 3 are nearly same)

4. If both the point 1 and 2 are true, that is, value of C4 > D4 > E4 > F4 > G4 and value of C6 > D6 > E6 > F6 > G6, then the merged cell A1 will show the value " Student is decreasing in (Value of Cell B4) and (Value of B6)"
For example, "Student is decreasing in English and Maths"

5. If all the point 1 and 2 and 3 are true, that is, value of C4 > D4 > E4 > F4 > G4, value of C6 > D6 > E6 > F6 > G6 and value of C5>D5>E5>F5>G5, then the merged cell A1 will show the value " Student is decreasing in (Value of Cell B4), (Value of B6) and (Value of B5)"
For example, "Student is decreasing in English, Maths and Hindi"

(Again, point 4 and 5 are nearly same)

6. If all of the points are false, that is, there is no any decreasing sequence (as shown in range C5:G5), then the merged cell A1 will show the value "No Remarks"

Important:
All of the above conditions are applied only if there are minimum 3 consecutive cells which are in descending order.
For example, cells D5, E5 and F5 have values which are satisfied all the three condition, i.e, they are in descending order, and they are consecutive (side by side), and they are minimum three.


(Please find the attachment below to understand the whole picture clearly)

Thanks in advance!

[DocAElstein]

Hi,
This is probably a question that is half a VBA question and half a bit of logical thinking.
I don’t think it is difficult, but just requires a bit of tedious coding.

I will give you some ideas, first. Tomorrow, if you still need help, I will give you a solution, based on the logic below:


My initial thinkings
My logical thinking is not necessarily the best. I am sure if you think about this problem long enough then you can come up with lots of different ways to do it .

My initial thinking is that you build up an array ( list ) of flags for each row. Anything will do, like for example a 1 could indicate that student was decreasing in that row .
Then you would loop through that array ( list ) , and build up the string to be pasted out.

To explain that pictorially : I am talking about producing something like this shown in yellow:

ENGLISH
50
45
30
20
15
1

HINDI
45
60
40
50
65
0

MATHS
70
55
40
25
10
1





That yellow column would not necessarily be in a spreadsheet. It could be built up in an array in the coding, ( pseudo like: my1sArr() = {1,0,1} ) , and that array then used in a loop internally to build up the string to be pasted out to A1.
That is just my initial thinking.
Producing an array or list of those 1’s can easily be done with some VBA coding.
Routinely when I would do such coding, that list would be produced in a way that it does not rely on a fixed table size. It is typical in such coding to calculate first the current Last row and last column. ( Typically in my codings I use the Long type variables, Lr and Lc

You want all this to happen automatically I assume, so you would need to have the coding in the worksheet event macro , Private Sub Worksheet_Change(ByVal Target As Range)
The initial code lines would typically be those to restrict the macro to running when you select within the table range. The very first code lines would need to determine the table range based on top left of B4 and bottom right based on a on determining Lr and Lc



I expect also that your problem could be solved by a clever formula. I have seen some very clever formula solutions at excelforum.com and mrexcel.com and eileenslounge.com to solve similar problems to yours. But I have no experience with formulas.

I will first have time again tomorrow to help further. If you do not get a solution in the meantime, then I will give you a macro to match the logic I described above

Alan



P.S.
If you want to make a start on a macro following my logic, then the first few code lines would be those required to calculate Lr and Lc
Those are the ones that typically take a form , pseudo like similar to
Lr = _.Range ( _ , _.Rows.Count ) .End(xlup) .Row
Lc = _.Cells ( _ , _.Column.Clount ) .End(xlToLeft) .Column

The next bit is to restrict the coding to work on the table range

[Anshu]

Thank you for the help and guidance!

I'm too new for VBA to create a long macro independently, and this is my struggle.

However, excel formula seems easier for me than VBA. Hence, as per your suggestion, I've tried to solve my problem with excel formulae (by trial and error method). And it tooks nearly 3 hours of time to frame this formula which works fine and satisfies all the points I've mentioned in the problem...


=IFS(AND(OR(AND(C4>D4,D4>E4),AND(D4>E4,E4>F4),AND(E4>F4,F4>G4)),OR(AND(C5>D5,D5>E5),AND(D5>E5,E5>F5),AND(E5>F5,F5>G5)),OR(AND(C6>D6,D6>E6),AND(D6>E6,E6>F6),AND(E6>F6,F6>G6))),"Student is decreasing in "&B4&", "&B5&" & "&B6,AND(OR(AND(C5>D5,D5>E5),AND(D5>E5,E5>F5),AND(E5>F5,F5>G5)),OR(AND(C6>D6,D6>E6),AND(D6>E6,E6>F6),AND(E6>F6,F6>G6))),"Student is decreasing in "&B5&" & "&B6,AND(OR(AND(C4>D4,D4>E4),AND(D4>E4,E4>F4),AND(E4>F4,F4>G4)),OR(AND(C6>D6,D6>E6),AND(D6>E6,E6>F6),AND(E6>F6,F6>G6))),"Student is decreasing in "&B4&" & "&B6,AND(OR(AND(C4>D4,D4>E4),AND(D4>E4,E4>F4),AND(E4>F4,F4>G4)),OR(AND(C5>D5,D5>E5),AND(D5>E5,E5>F5),AND(E5>F5,F5>G5))),"Student is decreasing in "&B4&" & "&B5,OR(AND(C4>D4,D4>E4),AND(D4>E4,E4>F4),AND(E4>F4,F4>G4)),"Student is decreasing in "&B4,OR(AND(C5>D5,D5>E5),AND(D5>E5,E5>F5),AND(E5>F5,F5>G5)),"Student is decreasing in "&B5,OR(AND(C6>D6,D6>E6),AND(D6>E6,E6>F6),AND(E6>F6,F6>G6)),"Student is decreasing in "&B6,TRUE,"")

The only issue is the length of the formula. As I've mentioned already that the actual table I have is made up of 10 Columns and 8 Rows (10x8). Only 3x5 cells require such a long formula, then what will be case if I'm going to frame the formula for 10*8 cells!! Because, with the increase of each new row, the formula is increasing like squre functions (exponentially). I doubt, the formula may cross the limit of excel formula length!

So, my question is, should I proceed with the formula, or I should see for VBA for solution??

[DocAElstein]

Hi,
I think your formula attempt looks like what I would have come up with when I first started using Excel.
I think I might be able to come up now with a slightly better formula, but it would not be much better.
( I had a worksheet full of many very long formulas like yours when I first started using Excel )

Now, a few years later, I personally prefer VBA, and I have not advanced much in my formula capabilities. But this is just my personal choice. At some of the other forums there are some extremely competent formula experts who could probably give you a very good formula solution. Some of those experts hate VBA.
It is all just personal choice of what you feel most comfortable with.

I think as time goes on you will decide yourself whether formulas, VBA, or some combination of the two are what you prefer.

I am not a computer expert. I am not very good at Maths. I find difficult formulas very hard to understand. I find VBA coding much easier.
I can’t help you much with a formula solution

( It would be interesting to see a good formula solution, for comparison. I can’t give you such a solution. If this was my project, I might try to get a formula solution, possibly from one of the other forums, just for comparison, if I had the time.
But its up to you. I think the best formula people are posting just now at mrexcel.com and eileenslounge.com.
excelforum.com also has some very good formula experts, but they seem to be taking a break from posting just now) )



This macro follows my original logic ideas. ( I am using a slightly different logic for the array, arrDec() , just because it was convenient to use True for a non decreasing row, and False for a decreasing row. Using your sample data , arrDec()= { False , True , False }

Public Sub Worksheet_Change(ByVal Target As Range)
Dim Lr As Long, Lc As Long
Let Lr = Me.Range("B" & Me.Rows.Count & "").End(xlUp).Row
Let Lc = Me.Cells(4, 2).End(xlToRight).Column ' I am using a slightly less common way including xlToRight because there are some explanation wordings that would be found giving a false number by the more typically used Columns.Count xlToLeft way
Dim RngTbl As Range: Set RngTbl = Me.Range("B4:" & CL(Lc) & Lr & "")
If Application.Intersect(Target, RngTbl) Is Nothing Then
Exit Sub ' I did not change anything in the table
Else
Let Application.EnableEvents = False
Let Me.Range("A1").Value = "No Remarks"
Let Application.EnableEvents = True
Rem Loop to get the array
Dim arrTbl() As Variant: Let arrTbl() = RngTbl.Value2
Dim arrDec() As Boolean: ReDim arrDec(1 To Lr - 3)
Dim Cnt
For Cnt = 1 To UBound(arrTbl(), 1) ' Loop "down" "rows" in table array
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
Let arrDec(Cnt) = True: Exit For ' we no longer have a decresing sequence
Else
End If
Next Clm
Next Cnt
End If
' at this point I have in my arrDec() False for a decreasing sequence and True for a non decreasing sequence
Rem loop to build the output string
Dim StrRemmark As String
For Cnt = 1 To UBound(arrDec())
If arrDec(Cnt) = False Then
Let StrRemmark = StrRemmark & " and " & arrTbl(Cnt, 1)
Else
End If
Next Cnt
' add remark
If StrRemmark <> "" Then
Let StrRemmark = Mid(StrRemmark, 6) ' this takes off the first " and "
Let Application.EnableEvents = False
Let Me.Range("A1").Value = "Student is decreasing in " & StrRemmark
Let Application.EnableEvents = True
Else
' no remmark
End If
End Sub
Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function



After I wrote that macro above , it became obvious to me that we don’t really need that extra array, arrDec(). Instead we can directly fill in the remark string.
This next macro version below seems to work just as well, and is simpler

Public Sub Worksheet_Change(ByVal Target As Range)
Dim Lr As Long, Lc As Long
Let Lr = Me.Range("B" & Me.Rows.Count & "").End(xlUp).Row
Let Lc = Me.Cells(4, 2).End(xlToRight).Column ' I am using a slightly less common way including xlToRight because there are some explanation wordings that would be found giving a false number by the more typically used Columns.Count xlToLeft way
Dim RngTbl As Range: Set RngTbl = Me.Range("B4:" & CL(Lc) & Lr & "")
If Application.Intersect(Target, RngTbl) Is Nothing Then
Exit Sub ' I did not change anything in the table
Else
Let Application.EnableEvents = False
Let Me.Range("A1").Value = "No Remarks"
Let Application.EnableEvents = True
Rem Loop to get the array
Dim arrTbl() As Variant: Let arrTbl() = RngTbl.Value2
Dim Cnt
For Cnt = 1 To UBound(arrTbl(), 1) ' Loop "down" "rows" in table array
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
Dim StrRemmark As String
Let StrRemmark = StrRemmark & " and " & arrTbl(Cnt, 1)
Else
End If
Next Cnt
End If
' add remark
If StrRemmark <> "" Then
Let StrRemmark = Mid(StrRemmark, 6) ' this takes off the first " and "
Let Application.EnableEvents = False
Let Me.Range("A1").Value = "Student is decreasing in " & StrRemmark
Let Application.EnableEvents = True
Else
' no remmark
End If
End Sub
Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function




Alan

( in the uploaded workbook are two worksheets for the two macros. – (You can only have one of a particular event coding in a single worksheet) )
P.S.
If you ever want a detailed explanation of anything in my coding, then please ask.
I can’t always respond quickly – often I only pop by the forums every 1-2 days. But I prefer to explain my coding in detail to help educate, rather than continually producing similar codings. ( That is just a personal preference. Many of the best forum helpers prefer to give a lot of coding quickly, enjoying solving as many problems as quickly as possibly . -
Its nice that the World is full of lots of different people and preferences, at least I think so..... )

[Anshu]

Thank you for the Macro. It works fine!
But there are 2 important points which I want to mention...

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)

Point 2) I've forgot one point to mention in my top post. So I edited my top most post after 3 hours of first posting. So, I think, the point got unnoticed. It's here...

Important:
All of the above conditions are applied only if there are minimum 3 consecutive cells which are in descending order.
For example, cells D5, E5 and F5 have values which are satisfied all the three condition, i.e, they are in descending order, and they are consecutive (side by side), and they are minimum three.
In the support of the line, I've edited the attachments too.
__________________________________________________ __________________________________________________ _______________________________________



If you ever want a detailed explanation of anything in my coding, then please ask.
I prefer to explain my coding in detail to help educate, rather than continually producing similar codings

How nice your idea is! I really like it.
But before asking for any explanation, I first need to understand at least 70 to 80 percent of the macro provided above. And for understanding this highly compex and complicated coding (for me) for a person like me, who has just started his journey of excel 3 months ago, it may took several days. First, I have to search the meaning of various terms used in the code above like .End(xlUp).Row, UBound etc. Then I have to search for the meaning of various lines used in the code above. Only then I'll consider myself eligible to ask for any explaination here.
Thank you very much for educating and improving my area of knowledge, surprisingly, without any cost!:)
In India, such person is referred as 'GURU'



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_- (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_-)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7EqbG23kg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7EqbG23kg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7KvJXmK 8o (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7KvJXmK 8o)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7E1gwg4Aq (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7E1gwg4Aq)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79hNGvJ bu (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79hNGvJ bu)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79YAfa2 4T (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79YAfa2 4T)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79M1SYH 1E (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79M1SYH 1E)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78SxhXT nR (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78SxhXT nR)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

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,

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

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/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)-Event-Coding?p=14915&viewfull=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/dotnet/visual-basic/language-reference/functions/string-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/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)-Event-Coding?p=14916&viewfull=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

[Anshu]

GREAT!!
The macro is working just awesome!

THANKS A LOT!!!


I would advise learning about the various string functions at an early stage of learning VBA https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/functions/string-functions )
Thank you for this link. I've added it to my bookmarks and will try to learn them as soon as possible.



We can easily change something like
MATHS
to
Maths
I really like and appreciate this change.

And I'm thankful for all these efforts, macros, help and guidance!!

[Anshu]

While working with the code, one small problem is detected, which is big for me, because I find myself unable to solve it.

When all the cells of the table (C4:G6) are empty, and I put any value in the first cell of the table (C4), it gives the following error...

Runtime error '9' : Subscript out of range

3419


On clicking on "Debug" option following line is highlighted in VBA

If arrTbl(Cnt, Clm + 1) >= arrTbl(Cnt, Clm) Then ' we no longer have a decresing sequence

3420

[DocAElstein]

Hi

The macro is based on a simple regular ( not jagged) 2 Dimensional range. In other words it is based on all rows having the same number of columns.
Any other situation is likely to cause problems. That problem that you have encountered may be the frist of many similar problems. Just solving that one problem won’t get us very far, as then tomorrow, or the day after, you will have another problem and so on, and so on…. A lot of time may be wasted writing macros that we then don’t need.

There are two things you need to do consider…
_1) Think very carefully of all the situations that may occur: Think of all the situations that might occur other than those resulting in a simple regular ( not jagged) 2 Dimensional range
_1b) Consider if the macro should only start when a regular ( not jagged) 2 Dimensional range , for example of minimum 2 columns of data occurs.
Will there likely be different numbers of columns?
Could there be empty columns within a row? – If so, how should that situation be handled??
Etc… etc…
_2) Think about whether an event macro ( one that starts automatically ) is the best for this situation. ( Event macros are actually quite advanced. When learning VBA one typically first meets event macros at a middle to advanced level )
Another possibility to consider is if it might be better to just have a normal macro. I mean by this a macro that does not start automatically. You would decide when to run the macro, for example via a button


I will have time to look again tomorrow. In the meantime, the more you can think of exactly what you want, and what situations might occur, the better chance we will have of getting it right without too many re writing of coding.


( A last thing to bear in mind. As the code given to you in a help forum becomes more complicated, it will be even more important for you to get to grips with learning and understanding the coding. In the future you wont have the support you might expect from having had more professional help..
Software is not as exact a science, as it probably should be, especially if Microsoft had anything to do with it!
The more complicated a coding is, the more chances are that problems occur in the future , even if you think nothing has changed. This is especially true if you choose to keep your Microsoft software up to date. Microsoft seem to be getting more and more incompetent and changes / updates seem to cause an increasing number of problems.
In short, it is getting more and more difficult to be just a user of software. To be a user, you need also to have some idea about the workings of any software you use… It probably should not be like that, but unfortunately it is… and increasingly so.
I am a builder of houses mostly these days. A manual labourer. . Previously I studied Physics at an advanced level, but I had no interest in computing. I needed Excel VBA for an important personal project a few years ago. I found, to my surprise, that it was not possible to use it effectively without getting quite knowledgeable about it.
I don’t understand why it is like that. One possibility is that most professional programmers are idiots, or they prefer to get things wrong, so that they will always be needed to repair the mess they make… )

Alan

[Anshu]

Hi!
I've corrected the problem by simply adding a simple error handling line "On Error Resume Next", and now it works fine.

Thank you for the suggestions! I'll keep remember those points.

[DocAElstein]

Hi
If you are happy with your solution and think it works consistently for you then that is fine. It is your responsibility so you should decide how to do things. That’s important.

But note:
Using On Error Resume Next , is in all but a few limited cases and for all but short code sections, generally regarded as very, very bad practice.
On Error Resume Next does not stop any errors
On Error Resume Next simply tells VBA to ignore them and keep running at the next line
Moreover, many problems and errors may occur, but On Error Resume Next instructs VBA to keep going, and this could occasionally lead to disastrous results.

Error handling in VBA
Error handling in VBA is one of the most advanced subjects, and is one area that is strangely only understood by just a very few number of people. Many experts and professional programmers do not understand fully how Error handling in VBA works
( I am one of about half a dozen people on this planet that fully understand how error handling in VBA works.
Anyone who understands how error handling in VBA works will only use On Error Resume Next in a very limited number of ways for very short code sections.
I once saw a very bad error in the use of error handling at a very famous Blog site. I tried to explain the problem to this person responsible who was one of the leading VBA experts. After two weeks he could not understand the problem, and , in frustration, reluctantly removed the entire Blog page at his site! )

At some time in the future, something unexpected and bad might occur when your macro is running. It might not be directly related to your coding. In this situation it will be very important for VBA to stop running your code and tell you in an error message what the problem is. Your use of On Error Resume Next will prevent VBA from taking the important error handling action. The code will simply keep going. This may be a very rare occurrence, but the consequences could be very serious.

Using On Error Resume Next is like instructing a car driver to always just keep driving regardless of any problems or accidents and to simply ignore everything and keep driving regardless of all the consequences.

In general, if it is possible, it is much better to prevent an error occurring, than to let it happen and ignore it.
On Error Resume Next tells VBA to ignore all errors anywhere . This is very bad programming practice
Somebody who understands error handling in VBA, will only reluctantly use On Error Resume Next to ignore a single error at a specific place in a coding if
_a) he is 99.999% sure that he understands the error and all the consequences , and
_b) he cannot figure out anyway to prevent that error occasionally occurring



Never the less, the solution for a short coding might mostly work most of the time.
Its your choice.

Alan

[Anshu]

Thanks for the insight regarding error handling! It'll be proved beneficial for me.

Currently I'm working on a project, free of cost, which, after completion, could be beneficial for 1162 Schools spreaded all over India. So, for learning anything new, I find very less time. That's reason, I'm trying to get some ready-made helps from the experts across different forums.

[DocAElstein]

That sounds interesting, its nice sometimes to know what lies behind requests for help.
As I said, it’s your choice and your decisions. Take any help , advice as you choose , but in the end its your project.

I could be wrong, but I think there is a chance that after such a project you could find about 1162 people looking at you for support. Or worse, if you continue to use On Error Resume Next in an uneducated way, then some people may be very angry with you when your coding breaks their computer. Important to remember is that most professional VBA programmers do not understand error handling in VBA. You will not find much accurate information or advice on the subject and many will falsely give you the advice that On Error Resume Next is OK to use.



My imprecision is that you have got actually some minimum understanding of the codings we have been discussing. That may be enough for you to proceed with your project.

I am sure that complete ignorance in VBA would make it impossible for anyone to successfully pass on any codings.
We do have an extreme example , a user , fixer (http://www.excelfox.com/forum/member.php/12517-fixer) at excelfox. In fact he has been posting under 100s of usernames in many forums for over 2 years. He has a very simple project, one which I know anyone could complete in 1-2 days after learning the basics of VBA for maybe 1-2 weeks.
But he has decided not to make any effort to learn any VBA. That is his choice
After 2 years he has made no progress what so ever. He is still asking almost exactly the same questions , even when he requires just a minor change that any small child could understand how to do.
All that he has achieved is to waste his time and many thousands of hours of helpers time. I do not think you are anything near this guy, but it does demonstrate what can happen when reluctance to take time to learn is a very false economy, leading to a much longer unnecessary time spent on a project.

[Anshu]

Yes! You're right. So, I've decided to remove the line On Error Resume Next, and to correct the issue which cause the problem, along with one more problem of blank cells, because blank cells, many a times, are treated by this macro as the cells having least value causing to produce result even when there are one last blank cell in three consecutive descending cells. It may take time for me, may be many days, but I think I can fix it, at least, to the working degree.

With thanks!
Anshu

[DocAElstein]

Learning by yourself trying, trial and error , and mistakes is probably the best way in the long run. But also getting help can help sometimes to reduce the time it takes. All our time is limited so helping each other, sharing experience and knowledge is good for us all.

... It may take time for me, may be many days, but I think I can fix it, at least, to the working degree.... Even if you don’t manage it, then usually, when you ask for help again , the fact that you tried may make it easier to understand when you get more help and have the final solution.


I think probably one of the best start points is to forget VBA and think carefully of exactly what you want to do, and try to think of all the possible scenarios/ situations that might occur. You might miss a few things, so there may still then be the need later to change things. But the more you try to think of all the different scenarios will reduce how many times you need to change / modify coding in order to get it perfect. Also thinking carefully about all you want to do before writing the coding will increase the chances that you can modify the coding slightly to make it perfect, rather than it be possibly better to completely re write a new macro

After you having re thought all the issues, you might decide that either
_ (i) A complete new macro is better. You may want to either try that yourself. Or ask again somewhere else. You should never rely on any one source of help. At least not when first you start learning.
Or
_(ii) you can modify the existing macro



If, having thought about it you choose _(ii) , then my initial thoughts are then a few ideas for you

If rows may have different number of column entries, then you need a different method to calculate the last column Lc
If you have no cells outside of the data table range to the right, then you can use what is probably the second most commonly used way to calculate last column Lc

Let Lc = Me.Range("B4:" & CL(Me.Columns.Count) & Lr & "").Find(What:="*", After:=Me.Range("B4"), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, Searchdirection:=xlPrevious).Column ' I am trying to find anything ( "*" is "wildcard" for anything ) in the range that is all row cells from column B in our data table range , I start at the first cell , I look for a value , I look at all text in the cell , I go through the columns , I go backwards which means that I effectively start again at the end of the range since I started at the first cell - the .Find will always go through the entire range and this trick makes sure I go through all cells starting at the last one - if i went forward I might hit an empty cell in the sequence - I could also have started at the last cell in the worksheet, assuming I never use the last cell in the worksheet

That code line will not work if you have anything to the right of your table data range. For example, in you supplied file you have some explaining text in column V . ( For example, "Student is decreasing in Maths" ) So that code line will return you 22, which is column V. If you remove all that explaining text to the right of your data table range , then that code line returns you 7 for column G
If you had the jagged data range below, then that code line returns you 9 for column I ( provided that you have nothing in any cells to the right of that table data range. B4:I6
_____ Workbook: Dynamic Table.xls ( Using Excel 2007 32 bit )
Row\Col
B
C
D
E
F
G
H
I

4ENGLISH
50
45
30
20
15


5HINDI
45
50
40
35
65
5
7


6MATHS
70
55
40
25
10
Worksheet: Sheet1 (4)

( By the way… You might want to change the code line that I gave you previously to calculate the last row, Lr , of your data range
This alternative will allow you to have text or anything below the data range, in column B , provided that there is at least one empty cell after the last entry in column B in your range
Let Lr = Me.Range("B4").End(xlDown).Row
That code line will give you the correct value , 6 , in you supplied file.
The original code line that I gave you,
Let Lr = Me.Range("B" & Me.Rows.Count & "").End(xlUp).Row
will give you an incorrect value of 18 in your sample file because you had text in row 18 outside your data range, in column B ( A BIG THANK YOU for the help!! ) )


The next step would probably be to arrange that nothing is ever done in any row having less than 3 columns of information , since then you can never have 3 consecutive decreases so there is not point in considering those rows.


The rest I can’t advise on, as I don’t know how you want to treat a situation where blank cells within a sequence occur.





In any case if you need more help, then feel free to ask here again, and / or anywhere else.




Alan

[Anshu]

Thanks for the insight! It'll help me to solve the problem. If it really takes longer time than usual, I'll return back for help, of course.

Thank you!!

[Anshu]

Hi!
Good Morning!

While working with the case, I realised that combination of both - excel formula and macro - can solve my problem effectively.
Hence, I've simplified the case with the help of formula. Almost half of the issue has been resolved, but the remaining half requires VBA to solve, because...(I'll explain the reason at last)

The simplified case is:

There are only 3 range - (F74:F81), (X74:X81), and (H40) to work with.
1. Range F74:F81 is linked with other cells, hence dynamic. Means, it may have any text.
2. Range X74:X81 can have only 2 value in each cell - either 1 or Empty ("") - set by formula.
3. Range H40 will be used for showing custom remark based on the value of range F74:F81 and X74:X81.
3422

Now the condition is,
(1) If Range X74:X81 is empty, Range H40 will show - "No Remarks"

(2) A) If Range X74 = "1" and all other cells are empty (X75:X81="") then, Range H40 will show "decline in (Corresponding cell value in Range F74:F81)" For example - decine in English
(There will be 8 condition when the Range X74:X81 will be empty except one cell which will contain 1.

B) Similarly, if there are two cells having value 1 in each, and remaining six cells are empty, Range H40 will show - "decline in (Corresponding cell value1) and (Corresponding cell value2)

....
....
....AND SO ON......

In short,
Whenever value "1" is appear in the Range X74:X81, and all the other cells are empty ("") in the same range, the corresponding value in the Range F74:F81 should be shown in Range H40 as remark, in the format - "decline in value1,value2,...value(n-1) and value(n)"

Important: Similarly if we extend this condition when there will be value 1 in the cell(s), and other cell(s) are empty, we got a total unique combination 8x7x6x5x4x3x2x1= 8! = 40320 conditions. No any excel formula is there to satisfy such a big number of conditions, as I know. This is the reason I'm looking for VBA.

I'm attaching here the sample file.

[DocAElstein]

Hi,
I suspect it could still be possible to achieve all you want with a formula based on some clever VLookUp and / or Index
I personally am unable to do that, - its beyond my formula abilities.
( see also note below *** )



So, to the VBA solution for your current situation
Your current situation is at a point similar to My initial thinking ( https://excelfox.com/forum/showthread.php/2633-Showing-Custom-Value-Based-on-the-Condition-of-Dynamic-Table?p=14910&viewfull=1#post14910 )
But you have now already got effectively that yellow column of 1’s – obtained from your formula.

So we already have close to the macro you need . – It is close to the first macro here , https://excelfox.com/forum/showthread.php/2633-Showing-Custom-Value-Based-on-the-Condition-of-Dynamic-Table?p=14912&viewfull=1#post14912 , the main difference being that now we don’t need to produce the arrDec() via logic in coding, - we can simply bring it in from the column that your formulas produce, X74:X81

If I understand correctly, you have fixed now your subject range size, so we don’t need to determine the number of subjects . If this is not the case, then the macro can be easily retuned to being dynamic to allow for any number of subjects, as it was previously.
We no longer need to consider the data range ( now K74:S81 ) in as much detail as previously , since all workings on that is now done by your formulas. But I assume the macro should be set off by changes in the range K74:S81

So this (https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)-Event-Coding?p=14957&viewfull=1#post14957)would be my next macro offering: ( I have modified the previous macro, so I leave you to “tidy it up” as you choose )
Macro is here: https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)-Event-Coding?p=14957&viewfull=1#post14957
I can’t test it thoroughly since your formulas do not work in my Excel versions***
( I use Sub Testie() to test my macro. This Sub Testie() simulates a change in your data range , K74:S81 . I can’t actually test in my Excel by changing a value in your data range, because that causes an error in your formula. – Your formula does not work in any of my Excel versions *** )

Alan





*** Important note: Your formulas are using the Excel function IFS
Your formula does not work in any of my Excel versions. ( I believe the Excel function IFS is only available since Office 2016 ).
If you share your formula solution to anyone with an earlier version of Office, then it won’t work!!!!
( I use Excel 2002, 2003, 2007 and 2010 only )

[Anshu]

Wow! THANKS for RE-Modification of the macro!
This is exactly what I was asking for. It works without any issue. I've modified the range according to my original table and "LCase" to "Application.Proper" to obtain subject case in proper case.
This code seems easy to understand. Also, this macro helps me to understand the previous macro which was complex for me till morning!


Thank you so much!!

Finally SOLVED!!

[Anshu]

( I believe the Excel function IFS is only available since Office 2016 ).
If you share your formula solution to anyone with an earlier version of Office, then it won’t work!!!!
( I use Excel 2002, 2003, 2007 and 2010 only )

Thanks for this information!
Since I need only two value, 0 or 1, in the range X74:X81, it can be simply achieved by using 'IF' formula also. So, in order to make this workbook compatible, I've replaced the IFS function with IF formula.

Thank you!