Search code examples
arraysvbaexcelsplit-function

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

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

enter image description here Thank you for your help with this ...really really appreciate any feedback!

Solution

  • 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