dcat2
03-06-2012, 09:08 PM
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
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