Loop through cells and add values into an array then display into activecell in VBA

Question

I've been struggling with this for a while and i need help. I am new to vba and would appreciate some guidance on the issue. My spreadsheet is set up so i am in cell a2 and am reading the value in b2 which is an integer that tells me how many supplemental profiles (sups) exist . Let's say the number 3 is in b2. Then i expect the 2 rows below c2 to have some text (role) entered in c3 and c4 as well.

While on a2, i am reading b2 for the number of supplemental profiles and then loop to display on a2 the roles found in c2, c3, and c4. This may change as in some cases there will only be 2 roles or 3, and so on.

To do this i have coded the following:

Sub testarray()

Dim sups As Integer
Dim role, resultrole As String
Dim test() As String

role = ActiveCell.Offset(i, 2).Value
sups = ActiveCell.Offset(0, 1).Value

i = 0
Do While i <= sups
 test() = Split(role)
 i = i + 1
 ActiveCell.Value = test()
Loop

End Sub

The problem is that i only get row c2 displayed and not all the contents below. I would like a2 to show values c2, c3, and c4. I would be running this code against many rows in order the roles associated.

See a pic of my code and spreadsheet with values i am reading

Thank you for your help with this ...really really appreciate any feedback!

I think you want `role` inside the loop but it's easier to use `cells(r,c)` instead of `offset`. — findwindow, Nov 09 '15 at 19:00
So continuing you logic, in cell A5 you would have the next 2? — Scott Craner, Nov 09 '15 at 19:00
Also I don't think split is what you want, the way you have it it will split a string on every space and fill an array with those values separated. You want to concatenate the values. — Scott Craner, Nov 09 '15 at 19:04

score 1 · Answer 1 · answered Nov 09 '15 at 19:11

You are only seeing the first value displayed in cell A, because you never tell the code to move anywhere but the active cell. You do have a loop, but it just iterates over the number.

The code below will loop through all cells in column B and write the roles into column A adjacent to where the sups number is stored. It will write the contents separated by a |. You can edit how you want.

I also took out the use of an Array. As you were using it, it would not give you what you want - which is a concatenated value. You could store the cell range into an array and loop through the array to write the cell value in column A, but there doesn't seem to be a real advantage to that given your problem.

Sub testarray()

Dim sups As Long, x As Long
Dim role As String

Dim lRow As Long

lRow = Range("B" & Rows.Count).End(xlUp).Row

Dim cel As Range

For Each cel In Range("B2:B" & lRow)

    role = ""

    If Len(cel) Then

        sups = cel
        For x = 1 To sups

            role = role & "|" & cel.Offset(x - 1, 1)

        Next

        role = Mid(role, 2, Len(role) - 1)

        cel.Offset(, -1) = role

    End If

Next


End Sub

Nice, and OP see if you can see what he did to remove the need for using `ActiveCell`. — BruceWayne, Nov 09 '15 at 19:17
This is great!! Thank you so much. Awesome...exactly what i want to do. I will study this snippet of code. Thanks!! — chepito77, Nov 09 '15 at 19:17
@chepito77 - please mark an answer if you found a satisfactory one so it can be useful to others going forward. — Scott Holtzman, Nov 09 '15 at 19:36

score 0 · Accepted Answer · answered Nov 09 '15 at 19:16

Some small tweaks to keep it as close to the original as I can. I prefer not to use ActiveCell, but @ScottHoltzman has a solution that does as such.

Sub testarray()

Dim sups As Integer, i&
Dim role, resultrole As String
Dim test As String

role = ActiveCell.Offset(i, 2).Value
sups = ActiveCell.Offset(0, 1).Value

i = 0
Do While i <= sups - 1
 If i = 0 Then
    test = ActiveCell.Offset(i, 2)
 Else
    test = test & ", " & ActiveCell.Offset(i, 2)
 End If
 i = i + 1

Loop
ActiveCell.Value = test
End Sub

i was able to incorporate this into the rest of my code with better ease...however, the piece above by Scott H may be more efficient — chepito77, Nov 10 '15 at 00:15

score 0 · Answer 3 · answered Nov 09 '15 at 19:16

This should work with the ActiveCell property one at a time.

Sub testarray()

    Dim sups As Long, roles As Variant

    With ActiveCell
        sups = .Offset(0, 1).Value
        ReDim roles(sups - 1)

        For sups = LBound(roles) To UBound(roles)
            roles(sups) = .Offset(sups, 2)
        Next v
        .Cells = Join(roles, Chr(10))
    End With

End Sub

Loop through cells and add values into an array then display into activecell in VBA

3 Answers3