Reformatting Data Error

[dcat2]

Currently I am scraping sports statistics into an excel sheet which is being formatted into one very long column. Each players name occurs multiple times with between 10-20 numbers listed beneath them before the next name appears. I want to have the numbers beneath each name reformatted so that they read across from the name. The code beneath is what I have worked out so far (the list of names is much longer but i shortened it for simplicity sake). I keep getting a "Next without For" error code on the next x. Can someone explain the problem that I have and/or suggest an alternative route?




Sub Reformat()

Dim i As Integer
Dim z As Integer

finalrow = Cells(Rows.Count, Columns).End(xlUp).Row
AriRoster = "Justin Upton" Or "Trevor Cahill" Or "Chris Young" Or "Aaron Hill"
For i = 1 To finalrow


If ThisWorkbook.Sheets("Sheet").Cells(1, i) = AriRoster Then

For z = 1 To 30


If ThisWorkbook.Sheets("Sheet").Cells(1, i + z) <> AriRoster Then
ThisWorkbook.Sheets("Sheet").Cells(z + 1, i) = ThisWorkbook.Sheets("Sheet").Cells(1, i + z)
Next z
Else: Next i
End If


Else: Next i
End If


End Sub

[GTO]

Greetings :hi: and welcome to vbaexpress:thumb

I'm sure you will like it here, as everyone is very helpful and especially to those who are really showing an interest in learning vba; as you obviously want to.

As to your code, at first blush, we have more than one problem I am afraid:eek: .

finalrow = Cells(Rows.Count, Columns).End(xlUp).Row

That is not going to work, as 'Columns' refers to the whole lot of them, and we want to pick just one column to run up from the bottom until we run into data (or slam into the top row).

AriRoster = "Justin Upton" Or "Trevor Cahill" Or "Chris Young" Or "Aaron Hill"

We also have an issue here. For certain, we are not going to be using OR like that on strings. I'd be guessing at this stage, so might as well just ask: What is it you want 'AriRoster' to be holding?

Finally, it appears to me that you are trying to split the If...Then...Else test between an outer and inner loop:buttkick: ...

I don't believe that'll be happening either.

Okay, I am done teasing and hope you are a good sport. How about attaching a workbook (preferably in .xls format) with a good sample of the data, "before" on one sheet and "after" on another. That way, we can see what you are wanting to accomplish and may offer a few suggestions as to how it can be accomplished.

Hope to help,

Mark