If cell contains value n, how can I create a colum

问题:

For the sake of simplicity, let's say I have a column of 5 numbers as follows:-

8
15
8
3
26

What I want to do is create a column composed firstly of 8 cells containing the value '8', then 15 cells containing the value '15', then 8 cells containing the value '8', and so on. This example would therefore result in a column length of 60 cells (however, my real-life column has over 200 numbers and would result in a column of about 2500 numbers!).

Is this possible? Would it need a formula or a macro? (I'm not very adept with either, I'm afraid, so apologies if this is a no-brainer.) Any suggestions very welcome.

回答1:

Try this

Public Sub Test()

    PopulateColumn Range("A1"), Range("B1")

End Sub


Public Sub PopulateColumn(ByVal rngSourceStart As Range, ByVal rngTargetStart As Range)

    Dim rngSource As Range
    Dim rngTarget As Range

    Set rngSource = rngSourceStart
    Set rngTarget = rngTargetStart

    Dim iValue As Integer
    Dim i As Integer

    While rngSource.Value <> ""
        iValue = rngSource.Value
        For i = 1 To rngSource.Value
            rngTarget.Value = iValue
            Set rngTarget = rngTarget.Offset(1, 0)
        Next
        Set rngSource = rngSource.Offset(1, 0)
    Wend

End Sub