Need help with excel autofill VBA style
 

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.

try something like this

sub putformulas
set mr=range("c2:c"&cells(rows.count,"c").end(xlup).ro w)
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


--
Don Guillett
SalesAid Software

"William Whitlam via OfficeKB.com" wrote in
message ...
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.

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



In article ,
"William Whitlam via OfficeKB.com" wrote:

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.

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

--
Message posted via http://www.officekb.com