Code for Controlling Cursor Movement

[Pete000]

First, my skills at coding VBA are limited. Having said that, I am the administrator of a fantasy football league and would like to automate the recording of players selected by each Owner during the league’s initial draft at the start of the season. So far I have developed a workbook with two worksheets which I have named “Draft Sheet”(where each Owner’s selection is recorder in cells C2:I13) and “Players” (a list of NFL players). My plan is to activate the cell of the Owner’s selection on the “Players” sheet and have my macro, via command button, select this player and paste it in the appropriate cell of the “Draft Sheet” then advance the cursor to the next selection position. I have written the code for the copy and paste aspect of the macro but have hit a snag instructing the cursor to its next position due to the odd order of the draft on the “Draft Sheet”. The required cursor movement follows. Starting in cell C2 proceed downward to cell C13 then over to cell D13 then proceed upward to cell D2 then over to E2 and then down again. This pattern of down, over, up and over repeats itself for columns F, G, H, and I. This is way beyond my skill level. Need help.

Please Login or Register  to view this content.

[Logit]

repeats itself for columns F, G, H, and I.

Explain ...

[Pete000]

Draft Sheet.jpg

Starting in cell C2 I would like the cursor to advance to the next cell (C2 to C3 to C4 down to C13 and then C13 to D13 up to D12 so forth an so on) as illustrated in the attached picture each time content is added to the active cell. The numbers in the cells indicate the order of the picks in the draft.

[Logit]

.
Paste this into a regular Module. Paste a CommandButton on the sheet connected to the macro. After clicking the command button, use the TAB key to move from one cell to the other.

Please Login or Register  to view this content.

[Pete000]

Logit thanks for your quick response. However, I have not explained my needs very well. So I'll take a different approach. Below I have attached my workbook for your review. First, I have added the Offset property to my above macro so the cursor advances down one cell in the same column. I have filled in the first ten draft picks by selecting one at a time and each in turn the players name from the "Players" worksheet and clicking on the "Copy & Paste Player's Name" command button. By selecting a player's name and clicking on the "Copy & Paste Player's Name" button the name is paste into the active cell on the "Draft Sheet" worksheet and the cursor advances down to the next cell and is ready for the next draft pick. All that needs to be done is go back to the "Players" worksheet and place the cursor on the name of the player selected by the next owner and click the command button. This is where I need help. After the 12th pick instead of the cursor moving down I need it to move to the right one column on the same row. Following this pick I need it to move up one cell at a time in the same column to the top cell where I need it to move right to the next column in the same row. After this pick it needs to move down one cell until it has advanced through all 84 cells in the range C2:I13 following the pattern shown in the image in my previous post. So what I need is code to replace the simple Offset script I've written that will advance the cursor as I've just described.