Simple last 10 games VBA help

[SPF]

Players are listed down columns A1:A7 For example let's use the player in A1

Wins are listed in A2
Losses are listed in A3

Each player has a command button for when they beat another player.

I only want to display the most recent 10 games. So if player A has 6 in A2 and 4 in A3 and then wins another game, I need the code to add 1 to A2 and then subtract one from A3, thus keeping the record at last 10 by showing 7-3.

I'm sure this is very simple, but I can't figure it out. Any help would be greatly appreciated.

 

[bertie]

Hi and welcome to the forum.

Do you really need code? Consider this layout:

Sheet1

*	A	B	C
1	Player	Win	Lose
2	aaa	1	9
3	bbb	5	5

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =10-B2 C3 =10-B3 <tbody> </tbody>

<tbody>
</tbody>

Where B2 is a validation list, i.e.,

Select cell B2
Select Data => Data Validation
In the "Allow" box select List.
type in 1,2,3,4,5,6,7,8,9,10
And click OK.
You can then drag the validation list down the column just as you would a formula.

 

[mikerickson]

You could use something like this. (

Range("B1").Value = 1 + Val(CStr(Range("B1").Value))
Range("C1").Value = Application.Min( 10 - Val(CStr(Range("B1").Value),  Val(CStr(Range("C1").Value) )