Error using Auto Fill of the class range

[Leonardo Koblitz]

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

 

[Tom Ogilvy]

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.

 

[Leonardo Koblitz]

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