Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i create a tennis ladder?
Ladder positions change based on challenge matches played. Example: Player
12 challenges Player 6 and wins. Player 12 now replaces Player 6 on the "Ladder" and Player 6 becomes 7. All of the players below 6 move down one rung. You shoud be able to print out the current "Ladder" as well as each individuals won/lost record. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i create a tennis ladder?
Hi,
Hope I have understood your requirement and this helps you on yor way: Sub TennisLadder() Dim Winner As String, Loser As String Dim wRow As Long, lRow As Long, iLastrow As Long Dim rng As Range, c As Variant ' Column A --- Name of Player ' Column B --- count of games played ' Column C --- count of games won ' Column D --- count of games lost ' ' Winner = "Player 2" ' Test Data Loser = "Player 7" ' Test Data iLastrow = Cells(Rows.Count, "A").End(xlUp).Row Set rng = Range("a2:a" & iLastrow) ' Assume header row With rng ' Find Winner Set c = .Find(Winner, LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then wRow = c.Row ' increment count of games played Cells(c.Row, 1).Offset(0, 1) = Cells(c.Row, 1).Offset(0, 1) + 1 ' increment count of games won Cells(c.Row, 1).Offset(0, 2) = Cells(c.Row, 1).Offset(0, 2) + 1 End If ' Find Loser Set c = .Find(Loser, LookIn:=xlValues) If Not c Is Nothing Then lRow = c.Row ' increment count of games played Cells(c.Row, 1).Offset(0, 1) = Cells(c.Row, 1).Offset(0, 1) + 1 ' increment count of games Lost Cells(c.Row, 1).Offset(0, 3) = Cells(c.Row, 1).Offset(0, 3) + 1 End If End With Rows(lRow).Select Selection.Insert Shift:=xlDown Rows(wRow + 1).EntireRow.Cut Rows(lRow) Rows(wRow + 1).Delete Shift:=xlUp End Sub "Gramps" wrote: Ladder positions change based on challenge matches played. Example: Player 12 challenges Player 6 and wins. Player 12 now replaces Player 6 on the "Ladder" and Player 6 becomes 7. All of the players below 6 move down one rung. You shoud be able to print out the current "Ladder" as well as each individuals won/lost record. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i create a tennis ladder?
Thanks for responding,
I'm sure your suggestion would help if I had a limited understanding of Visual Basics. My experience is limited to adding data, formulas and a few functions to Excel spredsheets. I have never tried to enter "code" nor do I know what to do with it after I type it in. Gramps "Toppers" wrote: Hi, Hope I have understood your requirement and this helps you on yor way: Sub TennisLadder() Dim Winner As String, Loser As String Dim wRow As Long, lRow As Long, iLastrow As Long Dim rng As Range, c As Variant ' Column A --- Name of Player ' Column B --- count of games played ' Column C --- count of games won ' Column D --- count of games lost ' ' Winner = "Player 2" ' Test Data Loser = "Player 7" ' Test Data iLastrow = Cells(Rows.Count, "A").End(xlUp).Row Set rng = Range("a2:a" & iLastrow) ' Assume header row With rng ' Find Winner Set c = .Find(Winner, LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then wRow = c.Row ' increment count of games played Cells(c.Row, 1).Offset(0, 1) = Cells(c.Row, 1).Offset(0, 1) + 1 ' increment count of games won Cells(c.Row, 1).Offset(0, 2) = Cells(c.Row, 1).Offset(0, 2) + 1 End If ' Find Loser Set c = .Find(Loser, LookIn:=xlValues) If Not c Is Nothing Then lRow = c.Row ' increment count of games played Cells(c.Row, 1).Offset(0, 1) = Cells(c.Row, 1).Offset(0, 1) + 1 ' increment count of games Lost Cells(c.Row, 1).Offset(0, 3) = Cells(c.Row, 1).Offset(0, 3) + 1 End If End With Rows(lRow).Select Selection.Insert Shift:=xlDown Rows(wRow + 1).EntireRow.Cut Rows(lRow) Rows(wRow + 1).Delete Shift:=xlUp End Sub "Gramps" wrote: Ladder positions change based on challenge matches played. Example: Player 12 challenges Player 6 and wins. Player 12 now replaces Player 6 on the "Ladder" and Player 6 becomes 7. All of the players below 6 move down one rung. You shoud be able to print out the current "Ladder" as well as each individuals won/lost record. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do i create a tennis ladder?
Hi,
Do you know how to get into Visual Basic and the Visual Basic Editor? If you don't, is there anyone who can help you? If not, if you want to send me an e-mail ) I'll put together some brief instructions to get you started annd/or send you a spreadsheet with the code in. But if you want to progress much further you may have consider learning some VB(Visual Basic for Applications). You will need to use this to solve more complex problems. HTH "Gramps" wrote: Thanks for responding, I'm sure your suggestion would help if I had a limited understanding of Visual Basics. My experience is limited to adding data, formulas and a few functions to Excel spredsheets. I have never tried to enter "code" nor do I know what to do with it after I type it in. Gramps "Toppers" wrote: Hi, Hope I have understood your requirement and this helps you on yor way: Sub TennisLadder() Dim Winner As String, Loser As String Dim wRow As Long, lRow As Long, iLastrow As Long Dim rng As Range, c As Variant ' Column A --- Name of Player ' Column B --- count of games played ' Column C --- count of games won ' Column D --- count of games lost ' ' Winner = "Player 2" ' Test Data Loser = "Player 7" ' Test Data iLastrow = Cells(Rows.Count, "A").End(xlUp).Row Set rng = Range("a2:a" & iLastrow) ' Assume header row With rng ' Find Winner Set c = .Find(Winner, LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then wRow = c.Row ' increment count of games played Cells(c.Row, 1).Offset(0, 1) = Cells(c.Row, 1).Offset(0, 1) + 1 ' increment count of games won Cells(c.Row, 1).Offset(0, 2) = Cells(c.Row, 1).Offset(0, 2) + 1 End If ' Find Loser Set c = .Find(Loser, LookIn:=xlValues) If Not c Is Nothing Then lRow = c.Row ' increment count of games played Cells(c.Row, 1).Offset(0, 1) = Cells(c.Row, 1).Offset(0, 1) + 1 ' increment count of games Lost Cells(c.Row, 1).Offset(0, 3) = Cells(c.Row, 1).Offset(0, 3) + 1 End If End With Rows(lRow).Select Selection.Insert Shift:=xlDown Rows(wRow + 1).EntireRow.Cut Rows(lRow) Rows(wRow + 1).Delete Shift:=xlUp End Sub "Gramps" wrote: Ladder positions change based on challenge matches played. Example: Player 12 challenges Player 6 and wins. Player 12 now replaces Player 6 on the "Ladder" and Player 6 becomes 7. All of the players below 6 move down one rung. You shoud be able to print out the current "Ladder" as well as each individuals won/lost record. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sports Comp Ladder tabulating help needed | Excel Worksheet Functions | |||
New to Charts - need ladder look | Charts and Charting in Excel | |||
Step Ladder Help | Excel Discussion (Misc queries) | |||
Ladder Help | Excel Worksheet Functions | |||
Help with a Ladder | Excel Worksheet Functions |