Keep W-L of card game played over time

[Preacherman771]

I have created a score worksheet for Hand & Foot Canasta for when my wife and I play. As we enter points for each hand it will keep track of total points, stating who is winning at the time by how many points. Once a player reaches a score greater than 10,000 total points, it will declare game over and the winner by how many points. I have also created a macro (Ctr-Q) which clears the worksheet of all points to start a fresh game once a winner is declared. What I would like to do now is somehow to keep a w-l tally for each player based on the result of the game. Normally I would create in the w-l cell under each player's name an if statement based on who won and who lost. What I am wanting to know is if since I use the clear macro I created after each game and then saving the cleared file, would there be a way of tallying who won and lost, use the clear macro but still have the players w-l record to date available next time the file is opened? e.g., Game 1: Player 1 is 1-0 and Player 2 is 0-1. When file is opened it would show that record and then add the result of Game 2, which player 1 lost and player 2 won thus the new records would be Player 1: 1-1 & Player 2: 1-1, before clearing the board and saving it.

Hand & foot score sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	HAND & FOOT SCORE SHEET
2	Team 1:	Pam								Pam			Roy				Pam Won the Game by 405 Points
3	Team 2:	Roy								12055			11650
4
5		RECORD
6		Pam			Roy								Deal
7		W	L		W	L				Pam			Pam			Pam			Pam
8		1	0		0	1			Points to Start:	50			120			120			GAME OVER
9									1st Finish	y	100					y	100
10									Red Books:	2	1000		2	1000		3	1500
11									Black Books:	3	900		2	600		4	1200
12									Wild Card Books:	y	1500					y	1500
13									Hand Points:		850			850			955
14									Total Points	4350			2450			5255
15										*	Dealt 1st					Deal
16										Roy			Roy			Roy			Roy			Roy			Roy			Roy			Roy
17										50			120			120			GAME OVER
18									1st Finish				y	100
19									Red Books:	2	1000		3	1500		1	500
20									Black Books:	2	600		2	600		2	600
21									Wild Card Books:	y	1500		y	1500		y	1500
22									Hand Points:		755			845			650
23									Total Points:	3855			4545			3250
Sheet1

Cell Formulas
Range	Formula
Q2	Q2	=IF(AND(J3<10000,M3<10000), (IF(J3>M3,J2&" Is Winning by "&J3-M3&" Points",M2&" Is Winning by "&M3-J3&" Points")), IF(AND(J3>=10000,J3>M3),J2&" Won the Game by "&J3-M3&" Points", IF(M3>=10000,M2&" Won the Game by "&M3-J3&" Points","")))
J2	J2	=J7
J3	J3	=SUM(J14:AF14)
M2	M2	=J16
M3	M3	=SUM(J23:AF23)
B6,E6	B6	=J2
K6,K15	K6	=IF(J6="*","Dealt 1st","")
B8	B8	=IF(AND(J3<10000,M3<10000),"", IF(OR(J3>=10000,M3>=10000), IF(J3>M3,1,0)))
C8	C8	=IF(AND(J3<10000,M3<10000),"", IF(OR(J3>=10000,M3>=10000), IF(J3<M3,1,0)))
E8	E8	=IF(AND(J3<10000,M3<10000),"", IF(OR(J3>=10000,M3>=10000), IF(M3>J3,1,0)))
F8	F8	=IF(AND(J3<10000,M3<10000),"", IF(OR(J3>=10000,M3>=10000), IF(M3<J3,1,0)))
M6,AE15,AB15,Y15,V15,S15,P15,M15,AE6,AB6,Y6,V6,S6,P6	M6	=IF(OR(J14="",M8="GAME OVER"),"", IF(J6="","Deal",""))
M7,AE7,AB7,Y7,V7,S7,P7	M7	=IF(OR(J8="GAME OVER",J7=""),"",$J$7)
M8,AE8,AB8,Y8,V8,S8,P8	M8	=IF(OR(COUNTIF($J8:L8,"*"&"Over"&"*")=1,COUNTIF($J17:L17,"*"&"Over"&"*")=1),"", IF(SUM($J23:L23)>=10000,"GAME OVER", IF(SUM($J14:L14)>=10000,"GAME OVER", IF(J14="","", IF($J$3<=2999,50, IF(AND($J$3>3000,$J$3<=6499),90,120))))))
K9,AF18,AC18,Z18,W18,T18,Q18,N18,K18,AF9,AC9,Z9,W9,T9,Q9,N9	K9	=IF(J9="Y",100,"")
K10,AF19,AC19,Z19,W19,T19,Q19,N19,K19,AF10,AC10,Z10,W10,T10,Q10,N10	K10	=IF(J10="","",SUM(J10*500))
K11,AF20,AC20,Z20,W20,T20,Q20,N20,K20,AF11,AC11,Z11,W11,T11,Q11,N11	K11	=IF(J11="","",SUM(J11*300))
K12,AF21,AC21,Z21,W21,T21,Q21,N21,K21,AF12,AC12,Z12,W12,T12,Q12,N12	K12	=IF(J12="","",IF(J12="Y",1500,-1500))
J14,AE23,AB23,Y23,V23,S23,P23,M23,J23,AE14,AB14,Y14,V14,S14,P14,M14	J14	=IF(SUM(K9:K13)=0,"",SUM(K9:K13))
M16,AE16,AB16,Y16,V16,S16,P16	M16	=$J$16
M17,AE17,AB17,Y17,V17,S17,P17	M17	=IF(OR(COUNTIF($J17:L17,"*"&"Over"&"*")=1,COUNTIF($J8:L8,"*"&"Over"&"*")=1),"", IF(SUM($J23:L23)>=10000,"GAME OVER", IF(SUM($J14:L14)>=10000,"GAME OVER", IF(J23="","", IF($J$3<=2999,50, IF(AND($J$3>3000,$J$3<=6499),90,120))))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M9:N13,P9:Q13,S9:T13,V9:W13,Y9:Z13,AB9:AC13,AE9:AF13,M18:N18,P18:Q22,S18:T22,V18:W22,Y18:Z22,AB18:AC22,AE18:AF22,M19:M21,M22:N22	Expression	=OR($M$8="GAME OVER",$M$17="GAME OVER")	text	NO
P9:Q13,S9:T13,V9:W13,Y9:Z13,AB9:AC13,AE9:AF13,P18:Q22,S18:T22,V18:W22,Y18:Z22,AB18:AC22,AE18:AF22	Expression	=OR($P$8="GAME OVER",$P$17="GAME OVER")	text	NO
S9:T13,V9:W13,Y9:Z13,AB9:AC13,AE9:AF13,S18:T22,V18:W22,Y18:Z22,AB18:AC22,AE18:AF22	Expression	=OR($S$8="GAME OVER",$S$17="GAME OVER")	text	NO
V9:W13,Y9:Z13,AB9:AC13,AE9:AF13,V18:W22,Y18:Z22,AB18:AC22,AE18:AF22	Expression	=OR($V$8="GAME OVER",$V$17="GAME OVER")	text	NO
Y9:Z13,AB9:AC13,AE9:AF13,Y18:Z22,AB18:AC22,AE18:AF22	Expression	=OR($Y$8="GAME OVER",$Y$17="GAME OVER")	text	NO
AB9:AC13,AE9:AF13,AB18:AC22,AE18:AF22	Expression	=OR($AB$8="GAME OVER",$AB$17="GAME OVER")	text	NO
AE9:AF13,AE18:AF22	Expression	=OR($AE$8="GAME OVER",$AE$17="GAME OVER")	text	NO
J8:AF8,J17:AF17	Cell Value	contains "GAME OVER"	text	NO

 

[Anonymous1378]

Sounds like you could add a short IF...THEN...ELSE statement before your clearing query, like

If Range("J3").Value2 > Range("M3").Value2 Then
    Range("B8").Value2 = Range("B8").Value2 + 1
    Range("F8").Value2 = Range("F8").Value2 + 1
Else
    Range("C8").Value2 = Range("C8").Value2 + 1
    Range("E8").Value2 = Range("E8").Value2 + 1
End If

 

[Preacherman771]

Thank you for your assistance. I entered the VBA code you suggested however when I hit F-5 (run) I continually receive "Compile error: Invalid outside procedure" and within the code the ("J3") entry portion highlighted.

 

[lrobbo314]

Did you put it into a subroutine like below?

Sub TallyPoints()
If Range("J3").Value2 > Range("M3").Value2 Then
    Range("B8").Value2 = Range("B8").Value2 + 1
    Range("F8").Value2 = Range("F8").Value2 + 1
Else
    Range("C8").Value2 = Range("C8").Value2 + 1
    Range("E8").Value2 = Range("E8").Value2 + 1
End If
End Sub

 

[Preacherman771]

Changed code to your suggestion and it now works perfectly. Thank you.

 

[lrobbo314]

Thanks, but you should give the credit to @Anonymous1378.

 

[Preacherman771]

Well, in that case, thanks to both of you. My wife & I will greatly enjoy being able to keep track of "bragging rights".