Using VBA how can I make it take text and put it somewhere

[San Antonio Heat]

I have about 6, 000 teams in this xcel sheet. I would liek to try a small bit of code to take the list and run them from left to right. If ya notice Team 1 and team 2 uses 2 colums in A and B for their 20 team roster. Can I use some kind of code to make them list them from left to right, say team 1 will use line 6 only and team 2 will use only 17 only, so in 6 and 17 it will run 20 colums to the left and it will take the info from A nad B and put them in the proper spaces. Is this too hard?

Book1
	A	B	C	D
2	2003Draft
3
4	League1
5	League1Team1
6	Pick1:LaDainianTomlinson	Pick2:PlaxicoBurress
7	Pick3:AmosZereoue	Pick4:ChadJohnson
8	Pick5:QuincyMorgan	Pick6:ChrisChambers
9	Pick7:MoeWilliams	Pick8:AntonioBryant
10	Pick9:KerryCollins	Pick10:BubbaFranks
11	Pick11:MarcBulger	Pick12:JonKitna
12	Pick13:LaBrandonToefield	Pick14:Pittsburgh
13	Pick15:BobbyShaw	Pick16:JoshReed
14	Pick17:JerramyStevens	Pick18:JustinMcCareins
15	Pick19:WillisMcGahee	Pick20:AaronElling
16	League1Team2
17	Pick1:ClintonPortis	Pick2:EddieGeorge
18	Pick3:AmaniToomer	Pick4:OlandisGary
19	Pick5:DerrickMason	Pick6:MattHasselbeck
20	Pick7:AshleyLelie	Pick8:WesleyWalls
21	Pick9:Carolina	Pick10:CoreyBradford
22	Pick11:LarryJohnson	Pick12:DennisNorthcutt
23	Pick13:BillSchroeder	Pick14:DougJolley
24	Pick15:JoeyHarrington	Pick16:JohnCarney
25	Pick17:ShaunMcDonald	Pick18:MichaelBennett
26	Pick19:QuentinGriffin	Pick20:FreddieMitchell
Sheet1

 

[San Antonio Heat]

Re: Using VBA how can I make it take text and put it somewhe

The new table would look like so

Book1
	A	B	C	D	E	F
1
2	2003Draft
3
4	League1
5	League1Team1
6	Pick1:LaDainianTomlinson	Pick2:PlaxicoBurress	Pick3:AmosZereoue	Pick4:ChadJohnson	Pick5:QuincyMorgan	Pick6:ChrisChambers
7
8
9
10
11
12
13
14
15
16	League1Team2
17	Pick1:ClintonPortis	Pick2:EddieGeorge	Pick3:AmaniToomer	Pick4:OlandisGary	Pick5:DerrickMason	Pick6:MattHasselbeck
Sheet2

 

[SydneyGeek]

Re: Using VBA how can I make it take text and put it somewhe

Hi, try this.
Place this code in a new module.

Sub NameMover()
  Dim c As Range
  Dim i As Integer
  Selection.Resize(10, 2).Select
  Do Until IsEmpty(ActiveCell)
  i = 0
  For Each c In Selection
    ActiveCell.Offset(0, i) = c.Value
    If i >= 2 Then c.Clear
    i = i + 1
  Next c
  Selection.Offset(11, 0).Select
  Loop
End Sub

Select the first Pick1 in your list, then run the macro

Note: Assumes that the layout is exactly as posted.

Denis

 

[San Antonio Heat]

Re: Using VBA how can I make it take text and put it somewhe

Thanks man i will give it a try and see if I can modify it to do all 6,000. I needed a starer to get it rolling, Thanks again

 

[San Antonio Heat]

Re: Using VBA how can I make it take text and put it somewhe

worked awesome but with 1 problem, it placed the 19th and 20th player instead of in Column U and V, it put them many lines down in A and B again, the rest are perfect. Maybe its because I have 6,000? It did all of them, there are actually 50 leagues with 12 teams so its not 6,000 just the original list is 6,000 lines of space since they are all in Colums A and B

 

[SydneyGeek]

Re: Using VBA how can I make it take text and put it somewhe

Hi, just ran it again with a dummy recordset and it worked fine. Did you start on Pick 1 or on League 1 Team 1? That could have been the difference.

Denis

 

[San Antonio Heat]

Re: Using VBA how can I make it take text and put it somewhe

the code worked awesome what happened was as i said before there are 6,000 rows of data, I had 1 line missing around 3, 000 row and it was throwing it off. Picks 1 and 2 were missing so it would jump 5 into the 1 spot. Awesome qwork though, After I sorted them all with that code, I deleted all the blank spaces using teh edit go to special blank spaces, delete.

How can I learn to do a simple code like that? Better yet how can I understand what you are telling it to do? Would love to learn.

Here is the link to what youode did, great stuff
http://www.angelfire.com/empire/wwffl/Test/mkb4html.htm

 

[SydneyGeek]

Re: Using VBA how can I make it take text and put it somewhe

Hi, here’s a quick description of what the code does. The secret to making it compact is to find areas where the structure repeats, and turn those into loops. So here goes:

Original code --

Sub NameMover() 
  Dim c As Range 
  Dim i As Integer 
  Selection.Resize(10, 2).Select 
  Do Until IsEmpty(ActiveCell) 
  i = 0 
  For Each c In Selection 
    ActiveCell.Offset(0, i) = c.Value 
    If i >= 2 Then c.Clear 
    i = i + 1 
  Next c 
  Selection.Offset(11, 0).Select 
  Loop 
End Sub

Housekeeping --
Sub and End Sub are the placeholders for the macro. They tell Excel that this is one macro routine.
The Dim statements are where I tell the macro what variables I want to use, and what kind of information they represent. So, c is a Range because I will use it to represent cells in a selection, and i is an integer because I will use it for counting in the loops.

Selecting the picks --

Selection.Resize(10,2).Select

You selected one cell. This expands the selection to 10 rows by 2 columns (when you refer to a range in parentheses like that, the row is always first and the column is after the comma)

The main loop --

Do Until IsEmpty(ActiveCell)
…
Loop

Tells the macro to keep repeating everything between these lines until the ActiveCell (top left cell in the selection) is empty – you’re at the end of the data. This is the loop that jumps to each team in turn.
Inside this, we’re asking Excel to do the following:

i=0

Set the variable i back to 0 (it goes to 20 for each team and needs to be reset, so the logic for the following steps is always the same)

The second loop --

For each c in Selection
…
Next c

This will do the processing of each team, putting the Picks out to the right and deleting 3 to 20 once they have been moved.

ActiveCell.Offset(0,i)=c.Value

The ActiveCell is Pick 1 in each team, because that’s where you started in the first team and the jump (Selection.Offset(11,0).Select) lands you on the next Pick 1 until all teams have been processed.
The Offset bit is relative to this: 0 rows and i columns (i changes from 0 to 19 as you cycle through each cell in the selection). So this code tells Excel to go through each cell in the range and place it on the same row as Pick 1. The changing column value makes the values run out for a total of 20 columns.

If i>=2 Then c.Clear

We needed to clear any values that had been physically relocated – 3 to 20. That’s what this line does.

i=i+1

This line increments the counter by 1, so the Pick values end up in the correct columns and the correct values get deleted.

I hope that clears up what this code did. As for learning VBA code, the best place to start is to record a macro and then check out the code. It will give you a starting point but the recorded code is pretty inefficient, and you can’t record loops. You need a book that can take you through it – and this site has one of the best that I have seen. VBA and Macros with Excel.

Denis

 

[San Antonio Heat]

Re: Using VBA how can I make it take text and put it somewhe

thanks, I am learning some things now and your tutorial is great! Thanks for taking the time.