Loop to replicate values into an array

Question

I’m in a situation where I need to reproduce something in VBA and a bit stuck given my lack of understanding of object oriented language and VBA in general.

Problem:

I need to produce an array or vector based on a 2 column table.
The first range (column) contains unit counts.
The second range (column) contains numeric values.

I need to replicate the value based on the number of units.

For example,

if the first row contains 3 units with a value of $100 I need the array to contain $100, $100, $100.
This will need to be looped thru each row containing units.
So if row 2 contains 2 units with a value of $50
I need to complete array to be $100, $100, $100, $50, $50, and so on.

I understand this situation will require ReDim the array based on the total values. My struggle is I’ve been unable to figure out the nested for loops.

I get how to replicate the value based on the number of “units” like the below...

    ReDim arr(0 To x - 1)
    For i = 0 To x - 1
    arr(i) = rng.Offset(0, 1).Value
    Next

What is the best way to loop thru each row and replicate the values for each row in the range based on the unit count?

If anyone is familiar with R, I'm essentially looking for something that achieves the rep() function (e.g., rep(df$b, df$a)) and return the values in a single array.

Any help is greatly appreciated. Thanks

Loop to replicate values into an array

3 Answers