Filling Formula down???

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

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.

"B Smith" <bsmith1111@isp.com> wrote in message news:1114649159.089472.92460@f14g2000cwb.googlegroups.com...
>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
>

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

"B Smith" wrote:

> 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
>
>

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


--
Don Guillett
SalesAid Software
donaldb@281.com
"B Smith" <bsmith1111@isp.com> wrote in message
news:1114649159.089472.92460@f14g2000cwb.googlegroups.com...
> 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
>

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.


*** Sent via Developersdex http://www.developersdex.com ***