CODEWITHSUNDEEP
vbaexcelfor-loop
bballboy8
bballboy8

Reputation: 450

For loop is not looping in VBA

I am currently trying to parse the contents of cells using VBA. Here is my code:

Sub Button1_Click()
Dim integ As Integer
Dim counter As Range
Dim counts As Integer
Dim num As Integer
num = 2
counts = 0
Dim element As Variant
Dim count As Integer
Dim players(1 To 100) As String
Dim lineup As String
Dim salary As Integer
For Each counter In Range("F2:F11")
    starter_letter = "L"
    lineup = counter.Value
    Dim arr() As String
    arr() = Split(lineup)
    For Each element In arr
        If element = "PG" Or element = "SG" Or element = "SF" Or element = "PF" Or element = "C" Or element = "G" Or element = "F" Or element = "UTIL" Then
            counts = counts + 1
        Else
            players(counts) = players(counts) + element + " "
        End If
    Next element
    For count = 1 To 8
        letter = Chr(Asc(starter_letter) + count)
        Range(letter + CStr(num)).Value = Trim(players(count))
    Next count
    num = num + 1
Next counter
End Sub

I will include pictures to illustrate the problem.

I am trying to take the names out of each cell and assign them to different cells.

It works for the first cell I parse, but the counter doesn't proceed to the next cell. Any help or insight would be appreciated.

Here are the contents of each cell as requested:

SF Ben Simmons C Marc Gasol PG CJ McCollum F Nicolas Batum PF Enes Kanter G Terry Rozier SG Andrew Harrison UTIL Wayne Selden Jr.
PG Kemba Walker C Marc Gasol G CJ McCollum UTIL Nicolas Batum F Enes Kanter SG Terry Rozier SF Al-Farouq Aminu PF Channing Frye
PG Kemba Walker C Kristaps Porzingis UTIL CJ McCollum SF Nicolas Batum G Terry Rozier F Josh Jackson PF Dirk Nowitzki SG Dwyane Wade
F LeBron James C Marc Gasol UTIL CJ McCollum SF Jaylen Brown SG Terry Rozier G Josh Jackson PG Wayne Selden Jr. PF Marreese Speights
C Dwight Howard PG Kemba Walker SG CJ McCollum PF Enes Kanter F Bobby Portis G Terry Rozier UTIL Wayne Selden Jr. SF Jae Crowder
SF LeBron James PF Al Horford G CJ McCollum C Myles Turner SG Denzel Valentine PG Terry Rozier UTIL Wayne Selden Jr. F Marreese Speights
C Dwight Howard SG CJ McCollum G Elfrid Payton SF Nicolas Batum PF Enes Kanter F Wesley Matthews PG Terry Rozier UTIL Channing Frye
PG Kemba Walker G CJ McCollum UTIL Dennis Smith Jr. SF Brandon Ingram F Enes Kanter C Bobby Portis SG Terry Rozier PF Jarrett Allen
G Kemba Walker F Al Horford UTIL CJ McCollum SG Jordan Clarkson SF Nicolas Batum PG Terry Rozier PF Dirk Nowitzki C Jahlil Okafor
SF LeBron James C Joel Embiid SG Nicolas Batum UTIL Bobby Portis G Terry Rozier PG Wayne Selden Jr. F Jarrett Allen PF Marreese Speights

These are the cells that need parsed.

These are the cells that need parsed.

These are the output of the current VBA code.

These are the output of the current VBA code.

Upvotes: 0

Views: 1927

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

try this:

Sub Button1_Click()

Dim inpts As Variant
Dim players() As Variant

With Worksheets("sheet3")
    inpts = .Range("F2:F11").Value
    ReDim players(1 To UBound(inpts, 1), 1 To 10)
    Dim counts As Long
    counts = 0
    For counter = LBound(inpts) To UBound(inpts)
        Dim arr() As String
        arr = Split(inpts(counter, 1))
        Dim element
        For Each element In arr
            If element = "PG" Or element = "SG" Or element = "SF" Or element = "PF" Or element = "C" Or element = "G" Or element = "F" Or element = "UTIL" Then
                counts = counts + 1
            Else
                players(counter, counts) = players(counter, counts) & element & " "
            End If
        Next element
        counts = 0
    Next counter
    .Range("L2").Resize(UBound(players, 1), UBound(players, 2)).Value = players
End With
End Sub

Upvotes: 2

Related Questions