How to calculate best bowling figure (cricket)

[DocAElstein]

_...... continued from last post https://excelfox.com/forum/showthrea...ll=1#post15578

In most practical situation that last event coding would likely cause problems as it will be running many times unnecessarily: It runs every time you change anything anywhere in Sheet 3 ( This is not the problem discussed at the end of the last post: It's a seperate issue )

We would generally prefer to limit the coding to when we change something in a restricted area. We can achieve that in many ways.
Most conveniently, Excel gives us a way to know in the coding which range was changed: Just before the Event macro runs, the range object of the range changed is assigned to that variable Target in the first code line, Private Sub Worksheet_Change(ByVal Target As Range)
Often towards the start of the macro we would have some If thing to see if the area of interest was changed. I have done that in the uploaded file, Stats_Template Event_1.xls

In the macro I have done for you, the important line is this one:

   If Application.Intersect(Target, DtaRng) Is Nothing Then ' ------------------------------------------------------------

To explain
I have arranged that DtaRng is the range of column D and E with your run and wicket data in it.
Target , as I explained, is assigned by Excel already to be the range that was changed.
The Intersect function is used to return a range object equal to that where any supplied ranges ( in our case DtaRng and Target) coincide ( where they overlap or intersect ). If, however the ranges don’t overlap, then the Intersect function returns us Nothing. The range you change , Target , and the data range, DtaRng , will not overlap ( intersect ) if you did not change a cell in the data range.
So I am testing to see if it returns nothing.
__ If it returns Nothing, Then I Do Nothing
__ Otherwise ( Else ) the coding is done.

Here the full macro:

Private Sub Worksheet_Change(ByVal Target As Range)   ' Target will be filled just before this macro starts , with the range object of the cell or cells that were changed
Rem 0 In this section are some things and ideas that might be useful if we extend this coding to an efficient macro for all sections.
Dim N As Long ' For this macro this is a single value.  It might be a loop bound index variable in a further macro.
 Let N = 1
Dim StrtRw As Long: Let StrtRw = ((N - 1) * 23) + 1 '
Dim Rws As Long: Let Rws = 21
Dim DtaRng As Range: Set DtaRng = Range("D" & StrtRw + 1 & ":E" & StrtRw + (Rws - 1) & "") ' this will be the data range  in columns D and E in a section
    
   If Application.Intersect(Target, DtaRng) Is Nothing Then ' ------------------------------------------------------------
   ' Do Nothing
   Else ' We will come here if we changed something in DtaRng, because there was a overlap / (intersect) in Target and the data range, DtaRng
    Rem 1 Worksheets info
    Dim Ws3 As Worksheet: Set Ws3 = ThisWorkbook.Worksheets("Sheet3")
    Dim Mtchs As Long: Let Mtchs = 20
    Dim FstMtch As Long: Let FstMtch = 2
    Dim MxE As Long
    Rem 2
    '2a   =MAX(E2:E21)
    ' Let MxE = Evaluate("=MAX(E2:E21)") ' This basic formula will work, if you are lucky, but probably better to do it like in the next line
     Let MxE = Ws3.Evaluate("=MAX(E" & FstMtch & ":E" & FstMtch + (Mtchs - 1) & ")")
    '2b
    Dim MnD As Long: Let MnD = 9999
    Dim Cnt As Long
        For Cnt = FstMtch To FstMtch + (Mtchs - 1) Step 1
         If Ws3.Range("E" & Cnt & "").Value = MxE And Ws3.Range("D" & Cnt & "").Value < MnD Then Let MnD = Ws3.Range("D" & Cnt & "").Value
        Next Cnt
    Rem 3 Output
    ' Let Ws3.Range("M22").Value = MnD & "_" & MxE
     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
    End If ' -------------------------------------------------------------------------------------------------------------

End Sub

---

I am happy to help further and / or explain anything further, as long as you are not in a hurry, as I am only sporadically near the forum just now
( By the way, if you are new to forums, you may not know that there are some other more busy forums such as excelforum.com and mrexcel.com. These are often better if you need a quick answer as there are lots of people there all the time eager to give a quick solution. You may not always get such detailed explanation. If you have the time and want to learn more, then probably here at excelfox.com is better, but just now there are less people here, so you probably won’t get a quick response. )

Alan