help with the macro or with the formula

I

Igneshwara reddy

Hi,

I have 250 lines in A column and I need to paste them 20 times each line,
could you please help out with the macros.

For Eg:
If I have in A1, number as 12, these 12 should be repeated 20 times in the B
column.

Let me know how this can be done.
 
I

Igneshwara reddy

No, this is not my result.

If 50 is in cell A1, it should repeat 20 time in B column from B1:B20 and
accordingly for A2,A3,A4..................

My results should come as
A B
50 50
25 50
35 50
45 50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
25
25
25
25
25
25
25
25
25
25
25
25
25
25
25
25
25
25
25
25
 
I

Igneshwara reddy

Hi, your forumla was good but I have 150 lines in Column 'A; to use, I cannot
do it manually selecting each cell which leads to more time.

can I use A1:A150

let me know.
 
S

Sandy Mann

I don't quite understand what it is that you are saying. If you mean you
now want to copy each entry 150 times then change the formula to:

=CHOOSE(CEILING(ROW(),150)/150,$A$1,$A$2,$A$3,$A$4)

If you mean that you want to copy cells in Column A down Column B 20 at a
time to row 150 then use:

=IF(ROW()>150,"",CHOOSE(CEILING(ROW(),20)/20,$A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$A$7,$A$8))

If you do not have data in all of A1:A8 then use:

=IF(OR(OFFSET($A$1,FLOOR(ROW(),20)/20,0)="",ROW()>150),"",CHOOSE(CEILING(ROW(),20)/20,$A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$A$7,$A$8))

If you mean something else, then post back..

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
Top