Hi
You have understood the Logic and code working perfectly.
( I also like to write out in words what I am doing, to help me remember later what I did. I find the green comments that can go a long way off to the right useful. But that is all personal choice. Most people and all advanced programmers find the green comments annoying )
( Cnt would often be referred to as something like the loop bound index variable )
You probably guessed that MnD is set initially to some arbitrary large number, because otherwise its default value is 0 and the logic won’t work – It will always stay at zero.
Bear in mind that there will be thousands of different ways in VBA to do what you want to do. This way I chose was the first simple idea I came up with.
Originally Posted by
ahmedleo414
...... a way to auto update when the values are adjusted.....
OK, Lets talk about automating stuff…
You know that we have coding that helps us do things, and that, approximately speaking, we can say that the coding is kept in a “code module”. We can have lots of code modules, and the code modules you will be familiar with are sometime referred to as “normal code modules”
You may not know that we can also approximately say that Excel, or rather Microsoft Office, is just lots of coding in code modules. In allowing us access to VBA coding, Microsoft is letting us use the very stuff they use to make Microsoft Office work.
Microsoft prefer not to show us all their code modules and / or all their VBA coding in them, because otherwise we could just copy it all, run it, and never have to buy their software.
Microsoft let us see their worksheets object code modules. They don’t show us all their coding in them.
They do let us add some more coding into some of their existing macros, in particular to those macros often referred to as something like “Event coding” or “Event macros”.
For example, we can put some coding in the macro that kicks in every time something is changed in a worksheet.
The very simplest solution for your automation would be to put the existing coding I did for you in that existing Microsoft “Event macros”.
I have done that in the uploaded workbook , Stats_Template SimpleEvent.xls
That works, but it’s very inefficient.
In the next post ( https://excelfox.com/forum/showthrea...ll=1#post15579 ) , I will take it a bit further, coming a bit closer to the more usual way of doing things
Here are a few picks illustrating how I went about making Stats_Template SimpleEvent.xls
Right Click View Sheet3 object coding.JPG http://i.imgur.com/wmXpTdW.jpg
Right Click View Sheet3 object coding.JPG
Sheet3 Object Code Module.JPG http://i.imgur.com/dfgmGQo.jpg
Sheet3 Object Code Module.JPG
Select Worksheet macros.JPG http://i.imgur.com/ltVwoMH.jpg
( Ignore the Worksheet_SelectionChange macro that may open up )
Select Worksheet macros.JPG
Open worksheet change macro.JPG http://i.imgur.com/nHUUnAe.jpg
Open worksheet change macro.jpg
**Paste Coding inside existing Worksheet Change macro.JPG http://i.imgur.com/eiW8lgc.jpg
** Don’t include the first and last line bits ( – miss out Sub CricketWickets_1() and End Sub )
That is almost all you have to do. There is just one problem. A big problem. This problem catches people out a lot when using these sorts of macros.
To explain:-
This coding we see is just a text copy of the coding that is run every time a change is made to the worksheet.
The macro that runs writes a value into the worksheet ( it pastes into cell M22 ). That changes the worksheet. That causes another copy of our macro to run. That macro writes a value into the worksheet ( it pastes into cell M22 ). That changes the worksheet. That causes another copy of our macro to run. That macro writes a value into the worksheet ( it pastes into cell M22 ). That changes the worksheet. That causes another copy of our macro to run. That macro writes a value into the worksheet ( it pastes into cell M22 ). That changes the worksheet. That causes another copy of our macro to run. That macro writes a value into the worksheet ( it pastes into cell M22 ). That changes the worksheet. That causes another copy of our macro to run. That macro writes a value into the worksheet ( it pastes into cell M22 ). ……….. and so on, going on for ever, or more likely until either your computer or Excel crashes!!!!
We overcome this problem by temporarily disabling the event coding around the line that causes the problem. So we replace this
Code:
Let Ws3.Range("M" & FstMtch + Mtchs & "").Value = MnD & "_" & MxE ' ... using variables rather than hard coding 22, as I am thinking ahead a bit for us, for a final macro which does all sections
With something like this
Code:
Let Application.EnableEvents = False
Let Ws3.Range("M" & FstMtch + Mtchs & "").Value = MnD & "_" & MxE ' ... using variables rather than hard coding 22, as I am thinking ahead a bit for us, for a final macro which does all sections
Let Application.EnableEvents = True
Setting EnableEvents to false, effectively tells Excel not to start any more Event type macros. It's important to set it back to True, or else the macro will not run again the next time you change something in the worksheet. Whilst set to False , the problem code line will not cause another copy of the macro to be started
Reply With Quote
Bookmarks