Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Formula down???
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" wrote in message ups.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Formula down???
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Formula down???
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula (a1*a2) filling down becomes (a2*a3) i want (a1*a3) Help! | Excel Worksheet Functions | |||
Formula filling | Excel Worksheet Functions | |||
Formula filling???? | Excel Worksheet Functions | |||
help with filling in an array formula | Excel Discussion (Misc queries) | |||
Filling Down Formula | Excel Discussion (Misc queries) |