Anyone any good at VBA in Excel?

[woody69]

I'm putting together an Excel spreadsheet that can automatically calculate each players handicap based on their recent Stableford score and capture that score in an overall table.

I have put something simple together that does all that, but the issue I currently have is when a handicap is reduced between buffer zones.

As an example:

If the initial value is 21.3 (Cat 4) then I need to minus n*0.4 from this until it falls below 21 (Cat 3) and then do n*0.3


Unfortunately if n=3, this is what is currently happening in the sheet:


21.3-(3*0.4)
21.3-(1.2) = 20.1


This is obviously incorrect as it should be


21.3-(1*0.4) = 20.9
20.9-(2*0.3) = 20.3


or to put it another way


21.3-(1*0.4) = 20.9
20.9-(1*0.3) = 20.6
20.6-(1*0.3) = 20.3

I have thought about some Do While or Do Until loop that checks if a cell is TRUE or not, i.e. work out how many shots they are to be cut and keep taking 1 away until it gets to 0, but I'm struggling to get my head around it... Any tips/guidance?

 

[Region3]

My first thought would be to do it something like this.

handicap is obviously the players handicap
diff is the number below CSS

For i = diff To 1 Step -1
    
        Select Case handicap

            Case Is < 5.4
                reduction = 0.1
            Case 5.5 To 12.4
                reduction = 0.2
            Case 12.5 To 20.4
                reduction = 0.3
            Case 20.5 To 28.4
                reduction = 0.4
            Case Is > 28.4
                reduction = 0.5

        End Select
        
        handicap = handicap - reduction
        
Next i

 

[Region3]

P.S. In your case in the OP it would be 2 lots of 0.4 and 1 lot of 0.3

Category 4 handicaps start at 21 as you know, but that means 20.5 is still Cat4.

 

[Val]

Have a quick search online, there are a number of downloadable resources who have done this already.

 

[guest100718]

Have a quick search online, there are a number of downloadable resources who have done this already.

Yes but its less interesting than writing it yourself.

Although I must confess to borrowing many a PS script....

 

[Foxholer]

My first thought would be to do it something like this.

handicap is obviously the players handicap
diff is the number below CSS

For i = diff To 1 Step -1
    
        Select Case handicap

            Case Is < 5.4
                reduction = 0.1
            Case 5.5 To 12.4
                reduction = 0.2
            Case 12.5 To 20.4
                reduction = 0.3
            Case 20.5 To 28.4
                reduction = 0.4
            Case Is > 28.4
                reduction = 0.5

        End Select
        
        handicap = handicap - reduction
        
Next i

First Case should be 'Case is < 5.5'! Someone on 5.4 would never get a reduction!! Others are correct.

Should it also be 'For i = diff to 0'?

 

[Region3]

First Case should be 'Case is < 5.5'! Others are correct.

Should it also be 'For i = diff to 0'?

A. Well spotted

B. No, you want it to loop as many times as single reductions are required. A loop from x to 0 would be executed x+1 times. (I had the same thought though so tested it to make sure )

 

[Foxholer]

B. No, you want it to loop as many times as single reductions are required. A loop from x to 0 would be executed x+1 times. (I had the same thought though so tested it to make sure )

That's Ok then - equivalent to 'with test after'. I'm more used to 'with test before' logic!

 

[chrisd]

It's not rocket science !

It's harder!

 

[woody69]

My first thought would be to do it something like this.

handicap is obviously the players handicap
diff is the number below CSS

For i = diff To 1 Step -1
    
        Select Case handicap

            Case Is < 5.4
                reduction = 0.1
            Case 5.5 To 12.4
                reduction = 0.2
            Case 12.5 To 20.4
                reduction = 0.3
            Case 20.5 To 28.4
                reduction = 0.4
            Case Is > 28.4
                reduction = 0.5

        End Select
        
        handicap = handicap - reduction
        
Next i

Thanks for this, this is perfect. My problem with VBA is there are literally dozens of ways to do it and unless you have a thought in your head I don't know where to start! Cheers though mate, really appreciate it!

 

[Region3]

Thanks for this, this is perfect. My problem with VBA is there are literally dozens of ways to do it and unless you have a thought in your head I don't know where to start! Cheers though mate, really appreciate it!

You're welcome. I enjoy this stuff for my sins, just wish I'd got qualifications so I could get a job doing it!

Don't forget to change the first CASE line to <5.5 as foxholer mentioned.

 

[woody69]

You're welcome. I enjoy this stuff for my sins, just wish I'd got qualifications so I could get a job doing it!

Don't forget to change the first CASE line to <5.5 as foxholer mentioned.

Yes, added the correction. I've also added an IF statement before this to check if diff is less than zero and within or outside of buffer to determine if there should be a 0.1 increase!