For loop is not looping in VBA

Question

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 output of the current VBA code.

First the concatenate character is & in vba not + so change Range("F" + CStr(counter)) to Range("F" & counter) — Scott Craner
– Scott Craner, Commented Feb 1, 2018 at 19:52
& and + only work the same when it's text. If any of the values just happen to be numbers, VBA will add them together, not concatenate them. — Cindy Meister
– Cindy Meister, Commented Feb 1, 2018 at 19:56
Third: Learn about Cells() it will eliminate the need to convert number to strings when trying to set a column. just to have excel convert them back to numbers. It is like converting DC power to AC just to convert it back to DC to power your phone. — Scott Craner
– Scott Craner, Commented Feb 1, 2018 at 19:58

Scott Craner · Accepted Answer · 2018-02-01 20:47:06Z

2

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

edited Feb 1, 2018 at 20:47

answered Feb 1, 2018 at 20:24

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

bballboy8 Over a year ago

This line players(counter, counts) = players(counter, counts) & element & " " gave me an out of range error

bballboy8 Over a year ago

0. It is on the first iteration

Scott Craner Over a year ago

What is the value of elements?

bballboy8 Over a year ago

"SF" is the value of elements

bballboy8 Over a year ago

The update didn't run at all since the for loop uses "element" but the next control uses "elements"

|

Collectives™ on Stack Overflow

For loop is not looping in VBA

1 Answer 1

18 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

18 Comments

Your Answer

Sign up or log in

Post as a guest

Related