FIND and PASTE

[gary]

My worksheet has 64,000 rows.
3,400 cells in column A contain numbers '1' thru '78'.
(The other 60,600 cells in column are empty).

Depending on the number in a cell in column A, formulas
need to be pasted in the cells in columns B thru G.

For example:

If A1 contains '16', formulas go in cells B1 thru G17.
If A95 contains '6', formulas go in cells B95 thru G101.
If A256 contains '2' formulas go in cells B256 thru G258.

Is there a faster way to do this than doing EDIT/FIND and
PASTE manually for 3,400 times?

 

[Frank Kabel]

Hi
what formulas do you want to insert. If you may post an example formula
for your different ranges a macro should do this quite easily

 

[gary]

Frank,

I checked my original posting and saw no replies so I
posted a new posting "FIND and PASTE (re-stated)". In the
meantime, you replied to my original posting -- Sneaky!

How do I attach an example of my actual worksheet?

gary

-----Original Message-----

 

[Frank Kabel]

Hi
DON'T attach a file to this NG. Try to post some example rows in plain
text please. Most people won't open attachments or ignore such posts
right-away.

 

[gary]

Frank,

In this example, if O1 contains '16', formulas get pasted
in P1 through U17, respectively.

o1 = 16

P Q R S T U

=SUM(P217) =p1*I1 =p1*J1 =p1*K1 =p1*L1 =Q1+R1+S1+T1

=g2/N1 =p2*I1 =p2*J1 =p2*K1 =p2*L1 =q2+R2+S2+T2

=g3/N1 =p3*I1 =p3*J1 =p3*K1 =p3*L1 =q3+R3+S3+T3

=g4/N1 =p4*I1 =p4*J1 =p4*K1 =p4*L1 =q4+R4+S4+T4

=g5/N1 =p5*I1 =p5*J1 =p5*K1 =p5*L1 =q5+R5+S5+T5

=g6/N1 =p6*I1 =p6*J1 =p6*K1 =p6*L1 =q6+R6+S6+T6

=g7/N1 =p7*I1 =p7*J1 =p7*K1 =p7*L1 =q7+R7+S7+T7

=g8/N1 =p8*I1 =p8*J1 =p8*K1 =p8*L1 =q8+R8+S8+T8

=g9/N1 =p9*I1 =p9*J1 =p9*K1 =p9*L1 =q9+R9+S9+T9

=g10/N1 =p10*I1 =p10*J1 =p10*K1 =p10*L1
=q10+R10+S10+T10

=g11/N1 =p11*I1 =p11*J1 =p11*K1 =p11*L1
q11+R11+S11+T11

=g12/N1 =p12*I1 =p12*J1 =p12*K1 =p12*L1
q12+R12+S12+T12

=g13/N1 =p13*I1 =p13*J1 =p13*K1 =p13*L1
=q13+R13+S13+T13

=g14/N1 =p14*I1 =p14*J1 =p14*K1 =p14*L1
=q14+R14+S14+T14

=g15/N1 =p15*I1 =p15*J1 =p15*K1 =p15*L1
=q15+R15+S15+T15

=g16/N1 =p16*I1 =p16*J1 =p16*K1 =p16*L1
=q16+R16+S16+T16

=g17/N1 =p17*I1 =p17*J1 =p17*K1 =p17*L1
=q17+R17+S17+T17



Note: As the value in Oxxx changes, the number and
references of the formulas in columns Pxxx thru Wxxx get
adjusted accordingly.

 

[gary]

I guess there's no way to do automatically what I need to
do so I've started to do EDIT/FIND/PASTE manually 3,400
times to paste the blocks of formulas in my worksheet.
(As a result, my mouse-hand is already numb and I'm done
only half of my worksheet).

 

[Lady Layla]

record a macro


: I guess there's no way to do automatically what I need to
: do so I've started to do EDIT/FIND/PASTE manually 3,400
: times to paste the blocks of formulas in my worksheet.
: (As a result, my mouse-hand is already numb and I'm done
: only half of my worksheet).
:
:
:
:
: >-----Original Message-----
: >My worksheet has 64,000 rows.
: >3,400 cells in column A contain numbers '1' thru '78'.
: >(The other 60,600 cells in column are empty).
: >
: >Depending on the number in a cell in column A, formulas
: >need to be pasted in the cells in columns B thru G.
: >
: >For example:
: >
: >If A1 contains '16', formulas go in cells B1 thru G17.
: >If A95 contains '6', formulas go in cells B95 thru G101.
: >If A256 contains '2' formulas go in cells B256 thru G258.
: >
: >Is there a faster way to do this than doing EDIT/FIND and
: >PASTE manually for 3,400 times?
: >
: >
: >
: >
: >
: >.
: >