Need help with excel autofill VBA style

[William Whitlam via OfficeKB.com]

Hi everyone, I need help with autofill. The situation is that I have an
excel spreadsheet that contain 2 worksheets: sheet1 and sheet2. The sheet1
basically contains just raw data; while sheet2 contains a number formula
that are used for calculation.
<Sheet1> <Sheet2>
A B A B
1 2 1 1 Sum AVG
2 3 9 2 =SUM(Sheet1!A1:Sheet1!B1) =SUM(Sheet1!A1:Sheet1!B1)
3 7 1 3
4 8 2 4

The question is that I want to write a VBA script that will autofill
the formula in column A2 and B2 downward 3 times according to the number of
rows in sheet1. I need to do it automatically, because sometimes sheet1
will contain 1000 rows while other times it will contain 10000 rows of
data. Anyone got any idea how I can do this?

Many Thanks in Advance.

 

[Don Guillett]

try something like this

sub putformulas
set mr=range("c2:c"&cells(rows.count,"c").end(xlup).row)
with mr
.formula="=$B2/SUMIF($A:$A,$A2,$B:$B)"
'.formula=.value'to leave just the values w/o the formula
end with
end sub

 

[JE McGimpsey]

One way:

Public Sub FillDown()
Dim nRows As Long
nRows = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
With Sheets("Sheet2")
.Range("A1:B1").Value = Array("Sum", "Average")
With .Range("A2:B2")
.Item(1).Formula = "=SUM(Sheet1!A1:B1)"
.Item(2).Formula = "=AVERAGE(Sheet1!A1:B1)"
.AutoFill _
Destination:=.Resize(nRows, 2), _
Type:=xlFillDefault
End With
End With
End Sub

 

[William Whitlam via OfficeKB.com]

Thanks a lot Don and JE.Yours formula worked likes a charm. Thank you all
for the great effort.