CODEWITHSUNDEEP
vbaexcel
FrenchConnections
FrenchConnections

Reputation: 391

Print array values a variable number of times

I am trying to write a macro that will print out the values in an array depending on conditions in other cells. I have gotten the macro to print out one value in the array, but not the others. The spreadsheet looks like this:

Column 1 | Column 2
___________________
L1       |
L1       |
L2       |
L3       |
L1       |
L5       |
L1       |

The array looks like this: List = Array("Person1", "Person2", "Person3") and what I am trying to do is print Person1, Person2 etc. for every value that says L1 up to that last L1 value. It should look like the example below.

Column 1 | Column 2
___________________
L1       | Person1
L1       | Person2
L2       |
L3       |
L1       | Person3
L5       |
L1       | Person1

The macro below partially works, but it only prints one person, Person3. Any help would be appreciated!

Sub Practice()

Dim i, j, k As Integer
Dim List As Variant
Dim LastRow As Long, CountL As Long
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")
List = Array("Person1", "Person2", "Person3")

LastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row - 1

For i = LBound(List) To UBound(List)
For j = 2 To LastRow
    If ws.Cells(j, 3).Value = "L1" Then
        ws.Cells(j, 4) = List(i)
    Else 'Do Nothing
    End If
Next j
Next i

End Sub

Note that the "L" values are in Column C and the person names in Column D in the actual spreadsheet, which is why the columns in the macro don't match the columns in the sample data I added here.

Upvotes: 1

Views: 180

Answers (2)

omegastripes
omegastripes

Reputation: 12612

Take a look at the below example:

Sub Practice()

    Dim ws As Worksheet
    Dim List As Variant
    Dim LastRow As Long
    Dim i As Integer
    Dim j As Integer

    Set ws = ThisWorkbook.Sheets("Sheet1")
    List = Array("Person1", "Person2", "Person3")

    LastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    i = 0
    For j = 2 To LastRow
        If ws.Cells(j, 3).Value = "L1" Then
            ws.Cells(j, 4) = List(i Mod 3)
            i = i + 1
        End If
    Next

End Sub

Upvotes: 1

YowE3K
YowE3K

Reputation: 23984

Your code is currently repeating its actions for each value in the list, and each iteration is assigning a value to every L1 row, and overwriting what was written there in the previous iteration.

You actually need to keep a counter of which value from your array you want to write next:

Sub Practice()
    'You should declare the type of each variable, or else they will be Variant
    'Dim i, j, k As Integer
    Dim i As Integer, j As Integer, k As Integer
    Dim List As Variant
    Dim LastRow As Long, CountL As Long
    Dim ws As Worksheet

    Set ws = ActiveWorkbook.Sheets("Sheet1")
    List = Array("Person1", "Person2", "Person3")

    'You should fully qualify objects such as Range, Cells and Rows
    'LastRow = ws.Cells(Rows.Count, "C").End(xlUp).Row - 1
    LastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row '<-- not sure why you subtracted 1

    i = LBound(List)
    For j = 2 To LastRow
       If ws.Cells(j, 3).Value = "L1" Then
           ws.Cells(j, 4) = List(i)
           i = i + 1
           If i > UBound(List) Then
               i = LBound(List)
           End If
       End If
    Next j
End Sub

Upvotes: 0

Related Questions