Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error using Auto Fill of the class range
Hi,
I make a form with a insert button. Part of my code is to repeat a formula like =IF(G14="x";0;I14/PROCV(A14;Dolar! $B$4:$C$3680;2)) on the next cell. I use this command Set SourceRange = Range(Cells(row- 1, 10), Cells(row- 1, 10)) Set fillRange = Range(Cells(row- 1, 10), Cells(row, 10)) SourceRange.AutoFill Destination:=fillRange, This code works well in the first time, after that, when I press the insert button again, the process stop with the following message The method Auto Fill of class Range failed Sincerely, Leonardo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error using Auto Fill of the class range
If you are using Excel 97 with a Commandbutton from the Control Toolbox
Toolbar, then try setting the buttons takefocusonclick property to false. Anyway, this worked for me and may give you some ideas: Sub tester1() Dim rw As Long Dim sourceRange As Range, fillRange As Range rw = ActiveCell.Row Cells(rw - 1, 10).Formula = _ "=IF(G14=""x"",0,I14/Sum(A14,Dolar!$B$4:$C$3680,2))" Set sourceRange = Range(Cells(rw - 1, 10), Cells(rw - 1, 10)) Set fillRange = Range(Cells(rw - 1, 10), Cells(rw, 10)) sourceRange.AutoFill Destination:=fillRange End Sub You should use a comma if assigning the formula in VBA and using the Formula property - if using FormulaLocal, then you can use the semicolon. I didn't know what the function PROCV was, so I just used Sum. However, you said it works one time, so the formula shouldn't be the source of the problem unless it would cause an error because of where it is being entered. -- Regards, Tom Ogilvy "Leonardo Koblitz" wrote in message ... Hi, I make a form with a insert button. Part of my code is to repeat a formula like =IF(G14="x";0;I14/PROCV(A14;Dolar! $B$4:$C$3680;2)) on the next cell. I use this command Set SourceRange = Range(Cells(row- 1, 10), Cells(row- 1, 10)) Set fillRange = Range(Cells(row- 1, 10), Cells(row, 10)) SourceRange.AutoFill Destination:=fillRange, This code works well in the first time, after that, when I press the insert button again, the process stop with the following message The method Auto Fill of class Range failed Sincerely, Leonardo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error using Auto Fill of the class range
Hi Tom,
Unfortunately the error goes on, than I will try to explain better. In my worksheet I have 3 fields (date (cell A13), input (cell B13), the input field converted to dolar of the day cell(C13)). In my worksheet I defined de three titles on the row 12 and the data will begin on row 13. I am using the formula =IF(G13="x";0;H13/PROCV(A13;Dolar! $B$4:$C$3680;2)) in the row 13 bellow of the input converted field. The function PROCV is equivalent to the function LOOKUP.., that look for a date on a range, defined at the field date (A13) and return the value of the dolar, so I divide the value of the input field in B13 by the value of the dolar found. As I said before I am trying to use a form to insert the data using the code lines bellow. ex: Date Input input converted ---- --- =IF(G13="x";0;H13/PROCV(A13;Dolar! $B$4:$C$3680;2)) Sincerely, Leonardo -----Original Message----- If you are using Excel 97 with a Commandbutton from the Control Toolbox Toolbar, then try setting the buttons takefocusonclick property to false. Anyway, this worked for me and may give you some ideas: Sub tester1() Dim rw As Long Dim sourceRange As Range, fillRange As Range rw = ActiveCell.Row Cells(rw - 1, 10).Formula = _ "=IF(G14=""x"",0,I14/Sum(A14,Dolar!$B$4:$C$3680,2))" Set sourceRange = Range(Cells(rw - 1, 10), Cells(rw - 1, 10)) Set fillRange = Range(Cells(rw - 1, 10), Cells(rw, 10)) sourceRange.AutoFill Destination:=fillRange End Sub You should use a comma if assigning the formula in VBA and using the Formula property - if using FormulaLocal, then you can use the semicolon. I didn't know what the function PROCV was, so I just used Sum. However, you said it works one time, so the formula shouldn't be the source of the problem unless it would cause an error because of where it is being entered. -- Regards, Tom Ogilvy "Leonardo Koblitz" wrote in message ... Hi, I make a form with a insert button. Part of my code is to repeat a formula like =IF(G14="x";0;I14/PROCV(A14;Dolar! $B$4:$C$3680;2)) on the next cell. I use this command Set SourceRange = Range(Cells(row- 1, 10), Cells(row- 1, 10)) Set fillRange = Range(Cells(row- 1, 10), Cells (row, 10)) SourceRange.AutoFill Destination:=fillRange, This code works well in the first time, after that, when I press the insert button again, the process stop with the following message The method Auto Fill of class Range failed Sincerely, Leonardo . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '1004': AutoFill method of Range class failed | Excel Discussion (Misc queries) | |||
VB Error: Paste Special method of range class failed | Excel Programming | |||
unable to set formulaarray of range class ERROR when using Conditional Sum | Excel Programming | |||
error 1004 Select method of Range class failed | Excel Programming | |||
error 1004 - metod Copy of class Range was failed | Excel Programming |