How to calculate best bowling figure (cricket)

[DocAElstein]

Hi
My knowledge of cricket is not so good, but I think I see what you want to do.

From what you have said and comparing your image with your uploaded template file, it looks like Sheet 3 from your Template file ( https://excelfox.com/forum/showthrea...ll=1#post15574 ) is what we are interested in. I have added some sample numbers: (For the next time: Its always a good idea for you to supply some numbers with the typical results you want, as this makes things easier for us to see what you want, and also we can check any macro we do for you).

I will try to guide you a bit , but you will appreciate that VBA code writing is a massive subject, so I can’t do a year of tuition in a forum Thread. But I think it is very commendable that you want to try it yourself. I might have to talk you through code examples to get us moving a bit quicker though.

You have regular sections which simplifies a couple of points
_ more usually in uses of VBA coding, sizes of data may vary and we would need some initial coding to determine any particular data size. But in your case we can ignore that. I am not a computer professional myself, but I think the computer jargon is … we can “hard code” things in your case. This means we may have a lot of fixed numbers in our coding in places where more typically variables would be used. Those variables would initially in the initial coding be set to match the current data size. I might still use variables for those numbers, just as a conventional way of doing things. That’s personal choice really.
Example: You have 20 Matches, so likely some number like 20 is going to be used somewhere, but from personal choice I might decide to define a Long type variable, Mtchs , and assign the value of 20 to it. Other variables associated with data sections wont always be the same value, so using variables is a good idea for those, as we usually want to make a macro efficient and not coding for each section: More likely we would try to arrange that the same coding is used for all sections, and to enable this, somehow the values in the variables would be changed to suit the current section being worked on.
It is usually a good idea to set variables anyway, because, it is very easy for VBA coding to get in a mess and mix up worksheets and cells and end up doing things to the wrong cells. So its usually a good idea to keep variables set to a specific thing towards the start of any macro. Personal choice again, and I would tend to have some initial code section , I would section it under Rem 1 typically. Personal choice again. There are usually hundreds of different ways to write a VBA code to do something.
_ because you have regular sections we can quite easily arrange a macro so that things are automatically updated when data is added into a particular section. I would suggest we leave out making the thing kick in automatically initially, as we can easily modify a macro later to get this to happen.

That should all be more clearer when we've finished


Let me start with a macro for the first section, initially to see if I have understood correctly what you want. Then we can take it from there later, when I pop by again.

Rem 1
This is defining and setting some variables for my personal convenience

'2a
I am getting the max value in column E (wickets). There must be infinite ways to do this.
As Excel has an in built spreadsheet function, I will use that, as I assume the makers of Excel are better programmers than me and their way works very efficiently.
In VBA I can use any Excel spreadsheet function using the VBA Evaluate(“ “) function thing. This allows me to build up in the text string “ “ bit; a string formula: The basic formula I want is something like
=MAX(E2:E21)
In fact that will often work just like, Evaluate("=MAX(E2:E21)")
But that is dangerous, since, as I mentioned, VBA coding can easily get mixed up not knowing where exactly a cell might be, for example it might go to the wrong worksheet.
Also I want to use variables rather than fixed numbers, as I explained above.

'2b
This uses the simplest form of VBA coding to do the basic logic to get the Min value in column D ( Runs ). It is very inefficient as is it interacts a lot with the spreadsheet, and each interaction is like slamming the brakes on in a code run. But it’s the easiest to understand, a simple loop and it is almost self explanatory what’s going on

Rem 3
This simply gives you your wanted output in the correct place. I am using variables rather than hard coding actual numbers, as I am thinking ahead a bit for us, for a final macro which does all sections
( I use a _ rather than a – because Excel has a nasty habit of sometimes thinking that a number set is a date if you use a – which can give you some screwy results. If you must use a – then no problem , but we would need to add some extra stuff to make sure Excel does what we want , and not sometimes what it thinks we want)

Sub CricketWickets_1() ' https://excelfox.com/forum/showthread.php/2756-How-to-calculate-best-bowling-figure-(cricket)?p=15573&viewfull=1#post15573
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   ' If we are at a maximum E value row And if the value of D in this row is < the current held lowest D value, Then make the current held lowest D value =  to this value  
    Next Cnt
Rem 3 Output
' Let Ws3.Range("M22").Value = MnD & "_" & MxE
 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
End Sub

See how you get on with that, as a starter. If you run it, it should give you 32_3 in cell M22 using the sample numbers that I added
I am not on the computer so much just now, but I will pop by again tomorrow.

Alan