PDA

View Full Version : Handicapp help?



Barryj
02-15-2006, 02:01 AM
Is it possible to have a number, in this case 20 which is a handicapp A9, reduce or increase when a score is entered into C9, I have the formula to calculate the new handicapp D9, but I don't know how to have the handicapp number increase or decrease with each calculation.

C5 =Course Par
E9 decides if the adjustment should be minus or positive.

What needs to happen is that if in this case the handicapp number starts on 20 and is decreased by .8 the new handicapp number should be 19.2, so that when the next score is entered for this person it will be calculated from 19.2 not 20, everything that I have tried gives me a circular reference, any ideas would be very greatful.

mdmackillop
02-15-2006, 07:35 AM
Hi Barry,
Paste this code into the worksheet module. I've added a line to show a whole figure handicap, assuming they are rounded to the next whole number (I may be wrong in this).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "C9" Then [A9] = [A9] + [B12]
[A10] = CInt([A9] + 0.5)
End Sub

XLGibbs
02-15-2006, 08:12 AM
Unfortunately, what you are in essence trying to do is have a formula and a stored value in the same cell which won't work, so the code option is best for your sample.

It would be more prudent to have the handicap be a fixed calculation where all of the scores entered (without code, they would be stored elsewhere where they could be entered into a table format ) where the aggregate of scores entered is tied to the handicap calculation...

This obviously isn't golf, because a 20 handicap shooting 38 on a par 36 would get a much bigger reduction in handicap :eek:

mdmackillop
02-15-2006, 11:54 AM
BTW, My code adds the contents of cells A9 and B12 when a score is entered. This does not seem to produce the correct result as the handicap increases with a sub 36 score. I'm sure you can correct this.

As it appears you need a code solution, the whole of the calculation (whatever it should be) could be incorporated into the code module, if this is desirable.
Regards
MD

Barryj
02-18-2006, 12:47 AM
Ok, I have this working for the first row starting on row 9, I have tried to alter the Macro to include the sheet as displayed, how can I code it so that it works for the columns and rows as displayed in sheet 1.

When the score is under 36 0.1 is added and if the score is over 36 then the handicapp in column C is altered depending on the handicapp in column B.

mdmackillop
02-18-2006, 12:57 PM
I think your add/deducts are the wrong way round.
Confession: I didn't notice your hidden columns, so I incorporated all the calculations into the code. It's neater that way, anyway, I think.

Barryj
02-18-2006, 09:43 PM
The Macro that was posted in the workbook did not subtract, it only added, I have included the workbook again and in row 9 it shows the proper calculations that should happen, this is using the original macro, if this could be expanded to include all the rows displayed.

I Have also dispayed the hidden columns so that you can see how the calculation is done.

Hope this makes some sense of it. Thanks again.

mdmackillop
02-19-2006, 04:15 AM
Sorry about that.

I think this works as per your example.

Barryj
02-19-2006, 04:19 AM
I think you may have forgot to include the file?

mdmackillop
02-19-2006, 05:00 AM
Some problem with the file. Somehow it was over 2Mb in size. I've copied it without the formatting etc.

Barryj
02-20-2006, 03:43 PM
Thankyou very much works fantastic.

mdmackillop
02-20-2006, 03:55 PM
Happy to help.

Barryj
02-21-2006, 02:56 PM
While this code works great, I am having a problem that when I save the workbook and then reopen, the code won't run, but if I copy the workbook fom this site and put into new workbook it works fine, but when I save and reopen it again, it does not work, any thoughts on why this may be happening.

It seems once you save the workbook it deactivates the code.

mdmackillop
02-21-2006, 03:34 PM
Hi Barry,
Check your macro security is not set to High. Tools/Macros/Security.
Regards
Malcolm

Barryj
02-21-2006, 04:57 PM
Macro security is set to low, still the same situation. Anything else I could try?

mdmackillop
02-22-2006, 01:29 AM
I cannot replicate the behaviour you are finding.
This is a piece of debug code I use when creating macos. It checks to see if EventsEnabled is set to true, and if not, it corrects this. Can you use this to check for true on reopening the workbook.


Sub Enables()
MsgBox Application.EnableEvents
Application.EnableEvents = True
MsgBox "Events enabled = " & Application.EnableEvents
End Sub

Barryj
02-22-2006, 04:47 AM
Found Problem, was with computer, thanks for the help.