Copying Formulas to Multiple Rows

[fb250r]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Index	Site_ID	Ngbr_List	#ofNgbrs	Grids	Comment						DistanceAway(mi)	Site_ID	#ofNgbrs	Ngbr_List
2	0	AD60		5	5177		1.3517793	0.6964921					ADAM60	16
3	1		AD690		3769		1.3525405	0.6952412	0.0013805	4.7458632	8.7893386	5.5	ADAM60	16	ADAM690
4	2		CU04		1983		1.3569979	0.6989756	0.0047074	16.182886	29.970704	18.6	ADAM60	16	CUMB04
5	3		YO35		1607		1.3509595	0.6979344	0.0015733	5.4086408	10.016803	6.2	ADAM60	16	YORK35
6	4		CU02		1483		1.3602563	0.6972362	0.0065431	22.49347	41.657907	25.9	ADAM60	16	CUMB02
7	5		FK02		539		1.3552864	0.7006773	0.0049728	17.095099	31.660122	19.7	ADAM60	16	FRAN02
8	0	AD690		4	7913								ADAM690	16
9	1		AD60		4295								ADAM690	16	ADAM60
10	2		CU04		3174								ADAM690	16	CUMB04
11	3		CU02		3010								ADAM690	16	CUMB02
12	4		YO35		2564								ADAM690	16	YORK35
13	0	AL25		2	12589								ALLE25	16
14	1		WS03		5637								ALLE25	16	WEST03
15	2		AL64		3839								ALLE25	16	ALLE64
Sheet1

Columns G- L contain formulas that I want to paste down, but the blocks of data have varying row counts. I know I can just select the given number of rows and paste what I need, but I have so many rows to paste down to.


For Example:
B2 is a block of data with 6 rows associated to it. B8 has 5 rows of formulas. B13 has 3 rows of formulas.

I need to populate only the rows that are blank with formulas from the B2 thru Lxx.

I can copy manually, but I have quite a few rows and blocks. What I have been doing is copying and pasting one less row as I go down the list.

This may be a doozie!


Frank

 

[Barrie Davidson]

Frank, how about this code:

Sub CopyFormula()
'Written by Barrie Davidson

Dim DataRange As Range
Dim CopyRange As Range

Application.ScreenUpdating = False
Set DataRange = Range("A8:A" & Range("A65536").End(xlUp).Row)
Set CopyRange = Range("G2:L2")
For Each c In DataRange
    CopyRange.Offset(c.Value).Copy _
        Destination:=c.Offset(0, 6)
Next c
Application.ScreenUpdating = True
End Sub

 

[fb250r]

Hey Barrie,

I was just visiting your web page on VBA and Excel formulas. Nice Job!

I'll let you know how it turns out.

Forever grateful,

Frank

 

[fb250r]

Barrie,

You ROCK, my Canadian Friend!

I hope I can return the favor one day or help anyone else in need.

Kind Regards,

Frank
ray: &

 

[fb250r]

Barrie,

I tried to change the Data Range from 65565 to a smaller number like, 10,000 so that the program will execute faster and I get a RunTime error "13".

Any ideas?

Regards,

Frank

 

[fb250r]

Barrie Davidson wrote this great code for me but it needs a minor revision that I cannot seem to correct.

I would like the code to end its search for data based on the last used cell in Column A. Currently it searches down to A65565 and it takes to darn long.



Does anyone have any ideas?

Frank

 

[Yogi Anand]

Hi Frank:

In Barrie's code after the line ...

For Each c In DataRange

add

if c = "" then exit sub

Does it help?

 

[fb250r]

Yes.

Thanks Yogi!

 

[Yogi Anand]

You are Welcome Frank!