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.

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.

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.

Gramps & Topper, sorry to jump in, but this may help:

Gramps, you need to enter this code into the Visual basic Editor (VBE). You can get to the VBE from inside of excel by Tools-Macro-Visual basic Editor. Once you get in here, select your workbook (VBAProject (xxxxxx)) and choose Insert-Module. You will get a code window where you can paste Toppers code. Once you get the code entered, you can run the macro at anytime. There are several ways to do this, but for simplification purposes, you can click on Run-Run Sub/Userform or from the excel spreadsheet you can go to Tools-Macro-Macros... and select 'TennisLadder'.

Remember: your spreadsheet needs to follow the format that Topper has laid out for you.

> ' Column A --- Name of Player
> ' Column B --- count of games played
> ' Column C --- count of games won
> ' Column D --- count of games lost


Good Luck!

HTH

DejaVu

Originally Posted by Gramps

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.

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 (toppers@johntopley.fsnet.co.uk) 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.