Auto 'Session' Number [READ FOR MORE DETAILS]

[tht1dude]

TestWorkbook.xlsm

Hi everybody,

Ok this is a bit complicated so, stick with me here

Ok I have made an example workbook with the problem which I will then have to transfer to my real workbook.
So the problem:

I have a list of 'Players' each with different names. 2 pieces of information are gathered for these players (in the case of this example), and these are 'Match Number' and 'Session Number'.

If you look at the example workbook I've included, you'll see that the players in column A aren't in any specific order. Their corresponding information is in the other two columns B & C.

If you filter the fields at the top, you'll notice (or you might of already) that for every 10 matches, a new session is made.
Also, matches go up one by one (i.e. if Ted is on match 2, then his next match MUST be match 3, for that given session).
Therefore, 1 Session = 10 Matches
*Sessions also go up one by one, so after session1, will come session2 for that specific player.

A new session can ONLY be started if there are 10 matches.

Ok, so what's the problem? I want to automate the whole process of matches and sessions for the players.
So if Ted's last set of data is Match 3 for Session 2, then through a macro, Match 4 for Session 2 for Ted, will be entered.

Seems simple right!? Well, it gets complicated, because the player's data can be entered at random (i.e. Ted might enter two sets, then Bill will enter one, then etc.) So it becomes hard to define the 'last' set of data for a player.

What I have so far:
I've managed to work out a macro, which looks at the last set of data, checks to see if the player is the same as the one you're entering data for, then looks at the Match number, checks it's less than 10, if it is it enters the same session number and the next sequential match number, if its not (the last match number is = 10), then it adds 1 to the session number, and puts the match number as 1.

What I need:
If the Player you are entering data for isn't the same as the one in the last data set, then the macro needs to find the last data set for your player, and do the afore mentioned (see if it's a new session or not, etc.)

Additional info.: In this example, the player names must be entered manually, but in the real thing, the player names are entered as part of a macro, directly from a user form.

Whoa, that was a sh*tload!

Thanks for your help, and if I confused you, or you don't get something, please feel free to ask me below.

Cheers!

[p24leclerc]

Here is the macro you are looking for. I also simplified your macro. Look at it closely and you'll learn easier way to do things.

Sub NewSessionNumber()
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Sheet1").Select
'this line find the last row with data
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Select

'If players are the same.
If ActiveCell.Value = ActiveCell.Offset(-1, 0) Then

'If Match Number is less than 10.
  If ActiveCell.Offset(-1, 1).Value < 10 Then
    ActiveCell.Offset(0, 1) = ActiveCell.Offset(-1, 1) + 1
    ActiveCell.Offset(0, 2) = ActiveCell.Offset(-1, 2)
  Else 'If Match Number is 10.
    ActiveCell.Offset(0, 1) = 1
    ActiveCell.Offset(0, 2) = ActiveCell.Offset(-1, 2) + 1
  End If
'
Else 'If Players are not the same.
  i = 1
  'this search for the last occurence of the name from the bottom up
  While ActiveCell <> ActiveCell.Offset(-i, 0)
    i = i + 1
  Wend
'If Match Number is less than 10.
  If ActiveCell.Offset(-i, 1).Value < 10 Then
    ActiveCell.Offset(0, 1) = ActiveCell.Offset(-i, 1) + 1
    ActiveCell.Offset(0, 2) = ActiveCell.Offset(-i, 2)
  Else 'If Match Number is 10.
    ActiveCell.Offset(0, 1) = 1
    ActiveCell.Offset(0, 2) = ActiveCell.Offset(-i, 2) + 1
  End If
End If
Application.ScreenUpdating = True
End Sub

Pierre

[tht1dude]

Thank you so much Pierre! Life saver man, worked like a charm

Just one more thing, if a new Player is added, both Match number and Session number would have to be 1. Would this go after the 'Wend' script?

Gui.

[p24leclerc]

Here you go: I had to add an IF statement within the Do While Loop because their would be an error generated by the code trying to look before row 1. I also changed the While-Wend function with a Do While Loop in order to be able to get out of it with the Exit Do command which is impossible for the While-Wend function.

Sub NewSessionNumber()
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Sheet1").Select
'this line find the last row with data
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Select

'If players are the same.
If ActiveCell.Value = ActiveCell.Offset(-1, 0) Then

'If Match Number is less than 10.
  If ActiveCell.Offset(-1, 1).Value < 10 Then
    ActiveCell.Offset(0, 1) = ActiveCell.Offset(-1, 1) + 1
    ActiveCell.Offset(0, 2) = ActiveCell.Offset(-1, 2)
  Else 'If Match Number is 10.
    ActiveCell.Offset(0, 1) = 1
    ActiveCell.Offset(0, 2) = ActiveCell.Offset(-1, 2) + 1
  End If
'
Else 'If Players are not the same.
  i = 1
  'this search for the last occurence of the name from the bottom up
  Do While ActiveCell <> ActiveCell.Offset(-i, 0)
    i = i + 1
    If i = ActiveCell.Row - 1 Then GoTo New_player
  Loop
'If Match Number is less than 10.
  If ActiveCell.Offset(-i, 1).Value < 10 Then
    ActiveCell.Offset(0, 1) = ActiveCell.Offset(-i, 1) + 1
    ActiveCell.Offset(0, 2) = ActiveCell.Offset(-i, 2)
  Else 'If Match Number is 10.
    ActiveCell.Offset(0, 1) = 1
    ActiveCell.Offset(0, 2) = ActiveCell.Offset(-i, 2) + 1
  End If
End If
GoTo E_nd
New_player:
  ActiveCell.Offset(0, 1) = 1
  ActiveCell.Offset(0, 2) = 1
E_nd:
Application.ScreenUpdating = True
End Sub