Filling Formula down???

[B Smith]

I am trying to code a macro to fill a unique formula down until it
reaches the end of file, which will have changed every time I run the
macro. I can manually type in the formula and fill down- everything
works fine then. I haven't been able to get the macro to work yet,
though.

A B C
1 H H H <-- H denotes column heading
2 N 5 F F denotes formula I want to fill down
3 N 6 F N denotes name
4 N 7 F

Here's my function so far:


Function formula1()
Range("C2").Select
ActiveCell.Formula = "=$B2/SUMIF($A:$A,$A2,$B:$B)"'thanks to KDales for
formula

dim lastrow
Set EndCell = ActiveSheet.UsedRange
lastrow = EndCell(EndCell.Count).Row

Selection.AutoFill Destination:=Range("C2", "C" & cstr(lastrow)),
Type:=xlFillDefault
End Function


I always get an error when my macro calls this function: "Autofill
method of Range class failed." I know that there are many other posts
about filling down, but none of them seemed to help. My columns A, B,
and C will not be moving though the last row will vary every time, and
there will be no gaps in data in columns A or B. I'm using Excel XP on
Windows XP. I'm still a beginner at all this so thanks for your time.
Brett

 

[Stevie_mac]

Works for me!
Couple of suggestions to help you solve this.

* Always use Option Explicit at the top of your Code modules - it will help you find spelling mistakes & more.
* Try the following line ...
Selection.AutoFill Range("C2", "C" & CStr(lastrow)), 0
in place of ...
Selection.AutoFill Destination:=Range("C2", "C" & cstr(lastrow)), Type:=xlFillDefault

(If it works I'll explain why later)

Regards - Steve.

 

[Guest]

Brett

Firstly you probably shouldn't be using a function for this but rather a
sub. Functions should return a value rather than manipulating cells etc.

You can also populate your formula into all required cells without using the
autofill method something like this:

Sub Ins_Form()
Dim endRow As Long
endRow = Cells(Rows.Count, 1).End(xlUp).Row

Range(Cells(2, 3), Cells(endRow, 3)).FormulaR1C1 _
= "=RC2/SUMIF(C1,RC1,C2)"
End Sub

Hope this helps
Rowan

 

[Don Guillett]

To do it really fast try

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

 

[Brett Smith]

Thanks Steve- worked perfectly.

Rowan- couldn't get yours to work (had to change my formula slightly-
don't know the R1C1 thing very well) but I am going to play around with
it and see where I go.

And Don- I am going to look into yours- speed could really help.

Thanks guys for your help.