CODEWITHSUNDEEP
excelvba
Sozmarra
Sozmarra

Reputation: 13

Filling a column with multiple values

I have an unusual problem. I have a spreadsheet full of data ranging from columns A:J. I want to add a further column, K, and populate it with names of Staff. The idea is that each staff member is given an allocation of the spreadsheet. The numbers for the allocation are already calculated in another sheet, and referenced in this spreadsheet in columns M and N. M2 and down contains the staff names, N2 and down contains their corresponding allocation.

My problem is figuring out how to fill column K with this data. For example APerson (M2) has an allocation of 23(N2), I want to populate K2 (Row 1 being the header) to K24 with APerson's name. After that, I would like to allocate APerson2(M3) with say, 15(N3), which would then start at K25. I have a feeling it will involve loops but my VBA knowledge is patchy and looking around I can only find loops which fill with incremental numbers.

Edit: Apologies for not attaching any files; this was initially posted via my phone as stackoverflow is blocked at my work. I printed my work-in-progress code out so i'll type it up ASAP.

Edit 2: Here's my code so far. I've got the allocation working - kind of. 

Sub Allocate()
name1 = Range("M2").Value
countto = Range("N2").Value

Range("K1").Select
 Do
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Value)
    For Counter = 1 To countto
        ActiveCell.Value = name1
        ActiveCell.Offset(1, 0).Select
    Next Counter

name2 = Range("M3").Value
countto2 = Range("N3").Value

Range("K1").Select
 Do
    ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Value)
    For Counter2 = 1 To countto2
        ActiveCell.Value = name2
        ActiveCell.Offset(1, 0).Select
    Next Counter2
End Sub

Is there any way to make this automatically assign 'name1', 'name2' and so on, for a large number (20+) staff without having to manually type it all, or any tips on how to make it more efficient?

Upvotes: 1

Views: 805

Answers (1)

KC_
KC_

Reputation: 26

As I see you have some basic knowledge of VB and I'm a bit rusty, this is a rush job until someone gives you a better answer. It assumes that on your source sheet theree is a different name on each row consecutively and then a blank row.

enter code here

Sub Allocate()
i3 = 2     'counter for DESTINATION sheet
i1 = 2     'counter for SOURCE sheet

Do until sheets(source).cells(i1, N#).value = ""     'until the last name
    NAME = sheets(source).cells(i1, N#).value         'N#: column NUMBER containing names
    ENTRIES = Sheets(Source).cells(i1, C#).value      'C#: column NUMBER containing numbers
    For i2 = 1 to ENTRIES
        Sheets(Dest).ceklls(i3, 11).value = NAME             '11 is column K
        i3 = i3 + 1
    Next
i1 = i1 + 1
Loop

Upvotes: 1

Related Questions