Hockey Pool Updates - Need VBA Help

dcozzy

New Member
Joined
Sep 25, 2019
Messages
1
What I am attempting to is streamline my process of updating my hockey pool each season. It is a keeper league, and so each team can keep up to 4 players from their previous season's team. The other thing is that when a player is kept, his "draft pick value" is one round higher than his previous year's draft pick value. So, if a player is drafted in the 4th round in 2018, and the team owner would like to keep the player on his team for the 2019 season, that player's value becomes a 3rd round pick.

What I would like to do is, once I have each team's "keepers" is write a code which will:

a) transfer the information I have on the current tab to a new tab, and
b) create the new keeper value at (current value -1)

The data is set us with players in column A, a "K" in column B beside players who will be kept (i.e., only want to transfer players with a "K" beside them to the new tab, in the same cell row/column as they are currently), and the draft pick value in column C. As such:

Team A
KeeperValue
Player 1K4
Player 2
Player 3K10
Player 4
Player 5K6
Player 6K8
Player 7
Player 8
Player 9
Player 10
Player 11
Player 12
Team BKeeperValue
Player 1K5
Player 2
Player 3K8
Player 4
Player 5
Player 6K3
Player 7
Player 8
Player 9K11
Player 10

<tbody>
</tbody>


I have multiple teams, each team with 12 players.

Hopefully this is enough info to get some help!

Thanks
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
Code:
Sub dcozzy()
   Sheets("[COLOR=#ff0000]RC[/COLOR]").Copy , Sheets("[COLOR=#ff0000]RC[/COLOR]")
   With ActiveSheet
      .Name = "New RC"
      .Range("B:B").SpecialCells(xlBlanks).EntireRow.ClearContents
      With .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         .Value = Evaluate(Replace("If(isnumber(@), @-1,if(@="""","""",@))", "@", .Address))
      End With
   End With
End Sub
Change sheet name in red to suit
 

Watch MrExcel Video

Forum statistics

Threads
1,090,459
Messages
5,414,643
Members
403,540
Latest member
mmorejon1215

This Week's Hot Topics

Top