Forum Discussion
Help with VBA Code
Hello Experts...I am a novice, self taught, old golfer who has developed a workbook for calculating our golf game. The part I am struggling with is the calculation of team scores. I believe I need to have individual VBA macro buttons since the team game we play is different depending on how many golfers show up (typically 8-20).
The scores are entered in the following format:
The team score calculation needs to look at team #, gross score (yellow cells), calculate net score based on handicap (HDCP) and the number of scores needed for the team score.
Net scores are determined by taking the gross score and determining if the golfer is eligible for a handicap stroke. Example: Bob M has a 7 HDCP (cell G2), on the #1 thru #7 handicap holes (row 16), one stroke is subtracted from his gross score to determine his net score.
Team scores are reported relative to par (row 17) for the total 18 holes. The output should be a simple two column table with team # and 18 hole team score.
Typical team games are:
2 golfer team best ball net score - The lowest net score of the two players.
3 golfer team 2 out of 3 net score total - The lowest 2 net scores of the three players.
While there are additional games we play, if I can get these two programmed, I can use them as a guide to help script the remaining games.
The workbook is 182MB so I can't attach it (it does a number of other things; team set up, payout calculations, etc...).
Am I approaching this problem in the most efficient way or should I be focusing my efforts in a different direction?
Thank you!
That looks great and works when I added it to the workbook! Thank you for your help!
8 Replies
I have left 2 replies, but they are not showing up, am I doing something wrong?
Thank you, this is great!
Originally I sent a simplified version of the input data, so I've added the full sheet to your workbook (Sheet 2).
I don't fully understand the LAMBDA concept but will use it as a learning opportunity!
What I am unsure of if additional golfers are added, will the table automatically expand to accept the additional rows or will there be null values?
Given my limited knowledge, could you move the function to Sheet 2 and will I be able to simply copy it into my workbook?
My workbook is set up for 45 golfers, that is why the sheet looks different. You can put the output table anywhere as I will simply copy the value for use in other parts of the workbook.
Many thanks and I envy your knowledge!
So I chose to do this in a worksheet function. This can be a unique LAMBDA function and then you can have multiple LAMBDA functions for different games. In this case I made a function that should work for BOTH those games (i.e. take SUM and subtract worst Score for each hole)
here is a breakdown of the function:
=LET(pTeams, Table1[Team '#], pHDCP, Table1[HDCP], pRaw, HSTACK(Table1[[1]:[9]],Table1[[10]:[18]]), HDCP, HSTACK(H12:P12,R12:Z12), Par, HSTACK(H13:P13,R13:Z13), NetScores, pRaw-(pHDCP>=HDCP), NetTeam, MAP(UNIQUE(pTeams),LAMBDA(I, SUM(BYCOL(FILTER(NetScores,pTeams=I), LAMBDA(c, SUM(c)-MAX(c)))))), HSTACK(UNIQUE(pTeams),NetTeam))So I did format the raw data as a TABLE but not required.
Row 1 gets the column of Team #s, the column of player HDCP, the player Raw scores (1-18 without the 'IN')
Row 2 gets the HDCP (again without the extra space) and the Par (but I just realized I forgot to subtract Par, but that is easy enough)
Row 3 finds the Net Score for EACH player for EACH hole.
Row 4 loops through each TEAM and then loops through each HOLE and takes the SUM-MAX to find the Team's score for that hole
Row 5 outputs the list of Team#s and final Scores
That looks amazing! However, I don't understand the LAMBDA function so it is a great opportunity to learn!
I simplified the table I originally sent, so I copied the actual sheet into your workbook (only values as many of the cells are filled with formulas and data from other sheets). If possible, can you transfer this function to the sheet2? The output can be anywhere since I will copy the values to a different location.
Hopefully I will be able to copy your version directly it into my workbook without changing any of the cell addresses.
The workbook is set up to handle 45 golfers, if more or less golfers show up, will the table need to be adjusted in the formula or will the formula return a null value? This is why I have a player count at the top of the page so I can adjust the tables and ranges based on this value.
Again, thanks and I envy your skills!
first off, yes this new forum software has some delay/scan process that can sometimes take quite some time which is annoying.
second, i love and appreciate the attitude and hope you learn LAMBDA and new functions that can make your life easier. They are not too hard to learn when you break them down and learn them a little at a time.
I am attaching the updated sheet. you will notice the only thing I did was change the 1st 2 lines to point to the new ranges and used regular range references instead of the (table) structured reference.
