Hello I need a little help, I started to learn Excel VBA of a few and that's why I make this appeal to you.
I need a VBA code which to replace him the bottom one (that goes, but to enter a code of 20,000 lines seems too many, isn't it?)
On short, my problem is :
In an Excel sheet I have a table with basketball players and their stats at each match, only that they don't play all matches played by their teams in a season. After my calculations an NBA team could play about 111-115 matches in a season, putting here and all matches in Play Offs - 7 games x 4 phases. So I approximated at 120 stages played by each team, that I have placed them from D6:DS6, below from D7: DS20 I put the stages played by each player with their stats (from D7 to DS7 even specific which is the match he played by him from 1 to 120), which can be differently understood by Excel, that putting them next to each other as if they were played consecutively (This arrangement comes from another VBA code but I do not want to change it), to better understand what I want to say, I give an example : a player plays stages 1, 16-32, 34, 58-71 in a total of 33 stages, all this I want them placed under the number of the stage played by his team, ie 1, 16-32, 34, 58-71, I do not want them next to others exacly as I explained before, so between stages 1 and 16 I need 14 free spaces, between stages 32 and 34 I need 1 free spaces, between stages 34 and 58 I need 23 free spaces, but I don't want to do this thing manually and I can't figure amount how I could make it easier, without having to enter a 20000-row code
Sub MoveStagePlayedPlayer ()
Dim StagePlayedPlayer As Variant
StagePlayedPlayer = Sheet1.Range("DR7").Value
If StagePlayedPlayer = 120 Then
Sheet1.Range("DR7:DR20").Cut Range("DS7")
End If
StagePlayedPlayer = Sheet1.Range("DQ7").Value
If StagePlayedPlayer = 120 Then
Sheet1.Range("DQ7:DQ20").Cut Range("DS7")
ElseIf StagePlayedPlayer = 119 Then
Sheet1.Range("DQ7:DQ20").Cut Range("DR7")
End If
StagePlayedPlayer = Sheet1.Range("DP7").Value
If StagePlayedPlayer = 120 Then
Sheet1.Range("DP7:DP20").Cut Range("DS7")
ElseIf StagePlayedPlayer = 119 Then
Sheet1.Range("DP7:DP20").Cut Range("DR7")
ElseIf StagePlayedPlayer = 118 Then
Sheet1.Range("DP7:DP20").Cut Range("DQ7")
End If
StagePlayedPlayer = Sheet1.Range("DO7").Value
If StagePlayedPlayer = 120 Then
Sheet1.Range("DO7:DO20").Cut Range("DS7")
ElseIf StagePlayedPlayer = 119 Then
Sheet1.Range("DO7:DO20").Cut Range("DR7")
ElseIf StagePlayedPlayer = 118 Then
Sheet1.Range("DO7:DO20").Cut Range("DQ7")
ElseIf StagePlayedPlayer = 117 Then
Sheet1.Range("DO7:DO20").Cut Range("DP7")
End If
End Sub
The above code manages to move the string of rows about I spoke you, leaving the desired spaces, for example DR7 string: DR20 will be moved to DS7: DS20 only if in the DR7 cell is the specified number of the stage played by player, but I return to what I said, there are not many 20000 thousand lines
Thanks in advance for your help! I hope the explanation has been coherent?
I need a VBA code which to replace him the bottom one (that goes, but to enter a code of 20,000 lines seems too many, isn't it?)
On short, my problem is :
In an Excel sheet I have a table with basketball players and their stats at each match, only that they don't play all matches played by their teams in a season. After my calculations an NBA team could play about 111-115 matches in a season, putting here and all matches in Play Offs - 7 games x 4 phases. So I approximated at 120 stages played by each team, that I have placed them from D6:DS6, below from D7: DS20 I put the stages played by each player with their stats (from D7 to DS7 even specific which is the match he played by him from 1 to 120), which can be differently understood by Excel, that putting them next to each other as if they were played consecutively (This arrangement comes from another VBA code but I do not want to change it), to better understand what I want to say, I give an example : a player plays stages 1, 16-32, 34, 58-71 in a total of 33 stages, all this I want them placed under the number of the stage played by his team, ie 1, 16-32, 34, 58-71, I do not want them next to others exacly as I explained before, so between stages 1 and 16 I need 14 free spaces, between stages 32 and 34 I need 1 free spaces, between stages 34 and 58 I need 23 free spaces, but I don't want to do this thing manually and I can't figure amount how I could make it easier, without having to enter a 20000-row code
Sub MoveStagePlayedPlayer ()
Dim StagePlayedPlayer As Variant
StagePlayedPlayer = Sheet1.Range("DR7").Value
If StagePlayedPlayer = 120 Then
Sheet1.Range("DR7:DR20").Cut Range("DS7")
End If
StagePlayedPlayer = Sheet1.Range("DQ7").Value
If StagePlayedPlayer = 120 Then
Sheet1.Range("DQ7:DQ20").Cut Range("DS7")
ElseIf StagePlayedPlayer = 119 Then
Sheet1.Range("DQ7:DQ20").Cut Range("DR7")
End If
StagePlayedPlayer = Sheet1.Range("DP7").Value
If StagePlayedPlayer = 120 Then
Sheet1.Range("DP7:DP20").Cut Range("DS7")
ElseIf StagePlayedPlayer = 119 Then
Sheet1.Range("DP7:DP20").Cut Range("DR7")
ElseIf StagePlayedPlayer = 118 Then
Sheet1.Range("DP7:DP20").Cut Range("DQ7")
End If
StagePlayedPlayer = Sheet1.Range("DO7").Value
If StagePlayedPlayer = 120 Then
Sheet1.Range("DO7:DO20").Cut Range("DS7")
ElseIf StagePlayedPlayer = 119 Then
Sheet1.Range("DO7:DO20").Cut Range("DR7")
ElseIf StagePlayedPlayer = 118 Then
Sheet1.Range("DO7:DO20").Cut Range("DQ7")
ElseIf StagePlayedPlayer = 117 Then
Sheet1.Range("DO7:DO20").Cut Range("DP7")
End If
End Sub
The above code manages to move the string of rows about I spoke you, leaving the desired spaces, for example DR7 string: DR20 will be moved to DS7: DS20 only if in the DR7 cell is the specified number of the stage played by player, but I return to what I said, there are not many 20000 thousand lines
Thanks in advance for your help! I hope the explanation has been coherent?
