Copy text a number of given times down the page ?

FIELD

New Member
Joined
Feb 7, 2005
Messages
1
This may be hard to explain, but here we go.

I have Text in say A1
in B1 i put a multiplier number say 43
I would like the text A1 to copy itself 43 times down the page in row C1
Then...
I have text in A2
in B2 i have the number 12
I would like it to copy text A2 down C? following after A1's text

If i change the Multiplier number on the given field i wish for C1 to also reduce in length.

??? Can this be DONE ???

Thanks

Anthony Field
xlr8sys@tpg.com.au :eek:
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
Try this,
In cell c1
=IF(ROW()<=$B$1,$A$1,IF(ROW()<=($B$1+$B$2),$A$2,""))

copy this formula down to as many rows as will cover your maxium(b1+b2),
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
This may get you started:

C1 =IF((ROW(C1)-ROW($A$1))<$B$1,$A$1,IF((ROW(C1)-ROW($A$1)-$B$1)<$B$2,$A$2,""))

Copy C1 down.
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Try this:
In C1
=IF(ROW()<=$A$2,$A$1,IF(ROW()<=$A$2+$B$2,$B$1,""))

copy paste down
 

Artemus

Board Regular
Joined
Dec 30, 2004
Messages
92
Here is a VBA solution that may get you started. You would probably want to add code to ensure that cells B1 and B2 actually contain integer values, etc.

Sub Copier()
Dim intFirstCount As Integer
Dim FirstItem
Dim intSecondCount As Integer
Dim SecondItem
Dim intCounter As Integer

FirstItem = ActiveSheet.Range("A1").Formula
intFirstCount = ActiveSheet.Range("B1").Value
SecondItem = ActiveSheet.Range("A2").Formula
intSecondCount = ActiveSheet.Range("B2").Value
For intCounter = 1 To intFirstCount
ActiveSheet.Cells(intCounter, 3).Formula = FirstItem
Next intCounter
For intCounter = intFirstCount + 1 To intFirstCount + intSecondCount
ActiveSheet.Cells(intCounter, 3).Formula = SecondItem
Next intCounter
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi,
try the code
paste the code onto sheet module
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, ii As Long, LastR As Range, lastA As Long
With Me
    If Intersect(Target, .Range("a:b")) Is Nothing Or Target.Count > 1 _
        Or Not IsNumeric(.Cells(Target.Row, 2)) Then Exit Sub
        Application.ScreenUpdating = False
        .Range("c:c").Clear
        lastA = .Range("a65536").End(xlUp).Row
        For i = 1 To lastA
            For ii = 1 To .Cells(i, 2).Value
                Set LastR = .Range("c65536").End(xlUp).Offset(1)
                LastR = .Cells(i, 1)
            Next
        Next
        Application.ScreenUpdating = True
End With
End Sub
hope this helps
jindon
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,052
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top