Showing Custom Value Based on the Condition of Dynamic Table

[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 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
4	ENGLISH	50	45	30	20	15
5	HINDI	45	50	40	35	65	5	7
6	MATHS	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.
Annotation 2020-09-21 022825.jpg

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/showthrea...ll=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/showthrea...ll=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 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/showthrea...ll=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!