VBA Code for copying rows based on cell value

[anonymous1234]

Hi,

I am new to VBA coding.

Please can someone help me on how to code for copying rows based on cell value:

For example,

If cell value E14 = 3


E18: Happy
E19: Birthday
E20: Mate!

 

[anonymous1234]

Hi,

I am new to VBA coding.

Please can someone help me on how to code for copying rows based on cell value:

For example,

If cell value E14 = 3


E18: Happy
E19: Birthday
E20: Mate!

accidentally uploaded without finishing:

then based on cell value E14 = 3, the data will be displayed:

Happy
Birthday
Mate!

Happy
Birthday
Mate!

Happy
Birthday
Mate!

 

[Georgiboy]

Here is a UDF that will do that, not sure if it will work for your needs/ excel version:

Function rpt(r As Long, rng As Range) As Variant
    Dim x As Long, y As Long, z As Long
    Dim var As Variant
    Dim oVar() As Variant
    
    var = rng.Value
    ReDim oVar((UBound(var) + 1) * r - 2)
    
    For y = 1 To r
        For x = 1 To UBound(var)
            oVar(z) = var(x, 1): z = z + 1
        Next x
        If z <= UBound(oVar) Then
            oVar(z) = "": z = z + 1
        End If
    Next y
    rpt = Application.Transpose(oVar)
End Function

Used as below:

Book1
	E	F	G	H
14	3			Happy
15				Birthday
16				Mate!
17
18	Happy			Happy
19	Birthday			Birthday
20	Mate!			Mate!
21
22				Happy
23				Birthday
24				Mate!
Sheet1

Cell Formulas
Range	Formula
H14:H24	H14	=rpt(E14,E18:E20)
Dynamic array formulas.