I have 3 columns of data 1st being names then date and time and finally event
scores, I want to move the names colunm to rows but only taking one instance,
then leave the date and time in column format. Now I need to fill each row
with the score relivent without having a blank cell. I have tried using IF
statments but after the first cell has been used it moves over 1 cell and
down 1 cell to put the next event score. Please can you help.

Sub movetorows()

Rows(1).Insert 'Insert blank row to simplifiy coding

StartColumn = 5 'Start putting data into column E
NewColumn = StartColumn
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
If Range("A" & RowCount) <> "" Then
NewName = Range("A" & RowCount)
Score = Range("C" & RowCount)
Set c = Rows(1).Find(what:=NewName, _
LookIn:=xlValues)
If Not c Is Nothing Then
Set NewCell = c.End(xlDown).Offset(1, 0)
NewCell.Value = Score
Else
Cells(1, NewColumn) = NewName
Cells(2, NewColumn) = Score
NewColumn = NewColumn + 1
End If
End If
Next RowCount

End Sub

"REVILO" wrote:

> I have 3 columns of data 1st being names then date and time and finally event
> scores, I want to move the names colunm to rows but only taking one instance,
> then leave the date and time in column format. Now I need to fill each row
> with the score relivent without having a blank cell. I have tried using IF
> statments but after the first cell has been used it moves over 1 cell and
> down 1 cell to put the next event score. Please can you help.

Thanks Joel, Im some way to achieving my aim, however after running this code
all that happens is cell E1 has Name cell E2 has Score and A1 and A2 loop
through and enter final Name and Score in their recpective cells. I have
tried to make fine adjustments but my experience with VBA is limited. If I
may add further clarity, after running a pivot table on my data the table it
produced is what im aiming to achieve although I would prefer to build my own
data table. Thanks for yor efforts thus far have a great 2008. REVILO

"Joel" wrote:

> Sub movetorows()
>
> Rows(1).Insert 'Insert blank row to simplifiy coding
>
> StartColumn = 5 'Start putting data into column E
> NewColumn = StartColumn
> LastRow = Range("A" & Rows.Count).End(xlUp).Row
> For RowCount = 2 To LastRow
> If Range("A" & RowCount) <> "" Then
> NewName = Range("A" & RowCount)
> Score = Range("C" & RowCount)
> Set c = Rows(1).Find(what:=NewName, _
> LookIn:=xlValues)
> If Not c Is Nothing Then
> Set NewCell = c.End(xlDown).Offset(1, 0)
> NewCell.Value = Score
> Else
> Cells(1, NewColumn) = NewName
> Cells(2, NewColumn) = Score
> NewColumn = NewColumn + 1
> End If
> End If
> Next RowCount
>
> End Sub
>
> "REVILO" wrote:
>
> > I have 3 columns of data 1st being names then date and time and finally event
> > scores, I want to move the names colunm to rows but only taking one instance,
> > then leave the date and time in column format. Now I need to fill each row
> > with the score relivent without having a blank cell. I have tried using IF
> > statments but after the first cell has been used it moves over 1 cell and
> > down 1 cell to put the next event score. Please can you help.

Here is the result of running my code with test data. I start with data in
Range A1:C11. the code inserts a row so the data moves to A2:C12. The
results is
placed in columns E and F.

I don't quite understand the description of the problem you described in
your last posting. Is there otrher data in your worksheet besides the data
in columns A - C? Try place just column A-c data in a newworksheet and try
re-running the code.


A B C E F

1 Joel Gary
2 Joel 1/1/2007 123 123 10
3 Joel 1/1/2007 3 3 11
4 Joel 1/1/2007 4 4 12
5 Joel 1/1/2007 5 5 13
6 Joel 1/1/2007 6 6 14
7 Gary 1/1/2007 10 15
8 Gary 1/1/2007 11
9 Gary 1/1/2007 12
10 Gary 1/1/2007 13
11 Gary 1/1/2007 14
12 Gary 1/1/2007 15

"REVILO" wrote:

> Thanks Joel, Im some way to achieving my aim, however after running this code
> all that happens is cell E1 has Name cell E2 has Score and A1 and A2 loop
> through and enter final Name and Score in their recpective cells. I have
> tried to make fine adjustments but my experience with VBA is limited. If I
> may add further clarity, after running a pivot table on my data the table it
> produced is what im aiming to achieve although I would prefer to build my own
> data table. Thanks for yor efforts thus far have a great 2008. REVILO
>
> "Joel" wrote:
>
> > Sub movetorows()
> >
> > Rows(1).Insert 'Insert blank row to simplifiy coding
> >
> > StartColumn = 5 'Start putting data into column E
> > NewColumn = StartColumn
> > LastRow = Range("A" & Rows.Count).End(xlUp).Row
> > For RowCount = 2 To LastRow
> > If Range("A" & RowCount) <> "" Then
> > NewName = Range("A" & RowCount)
> > Score = Range("C" & RowCount)
> > Set c = Rows(1).Find(what:=NewName, _
> > LookIn:=xlValues)
> > If Not c Is Nothing Then
> > Set NewCell = c.End(xlDown).Offset(1, 0)
> > NewCell.Value = Score
> > Else
> > Cells(1, NewColumn) = NewName
> > Cells(2, NewColumn) = Score
> > NewColumn = NewColumn + 1
> > End If
> > End If
> > Next RowCount
> >
> > End Sub
> >
> > "REVILO" wrote:
> >
> > > I have 3 columns of data 1st being names then date and time and finally event
> > > scores, I want to move the names colunm to rows but only taking one instance,
> > > then leave the date and time in column format. Now I need to fill each row
> > > with the score relivent without having a blank cell. I have tried using IF
> > > statments but after the first cell has been used it moves over 1 cell and
> > > down 1 cell to put the next event score. Please can you help.