Need help with simple VBA Script

  • Thread starter Thread starter Donald Parker
  • Start date Start date
D

Donald Parker

I'm just learning this, and I am running into a problem I can not
understand.

I've written a VBA macro to put a value is a formula in a cell and then use
the autofill method to extend that formula to an adjacent range.

What I am finding is that the assignment of a formula works fine but the
autofill fails (run-time error 1004 autofill method of range fails).

e.g.
' this next statement works fine
Sheets("calculations").Range("b3").FormulaArray = "=SUM(IF('Raw
Data'!N$3:N$1776=$A3,1,0))"
'this next statement gives me the run time error
Sheets("calculations").Range("b3").AutoFill Destination:=Range("b3:e3"),
Type:=xlFillDefault

I don't think it is an autofill problem ... I've tried simpler things like
just trying to use the select method. I get the same symptoms - run time
error 1004 select method of range fails

e.g.
' this next statement works fine
Sheets("calculations").Range("b3") = "a"
'this next statement gives me the run time error
Sheets("calculations").Range("b3").Select

I've seen almost identical code generated with macro recorder that runs with
no problems. I'm baffled. An answer to this problem would be best, but
probably better advice would be how the heck to debug problems like this.

Thanks!
 
Donald

I've rewritten your code and this seems to work for data
in the same sheet
Sub test()
' this next statement works fine
Sheets("Sheet1").Range("b3").FormulaArray = "=SUM(IF
(sheet2!b$3:b$7=$A3,1,0))"
With Sheets("sheet1")
Range("b3").AutoFill Destination:=Range("b3:e3"), _
Type:=xlFillDefault
End With
Sheets("sheet3").Select
Range("b3").Select
ActiveCell = "a"
End Sub

Regards
Peter
 
That will work as long as Sheet1 is the activesheet, but using your with
statement has no effect on the result. You need to put periods in front of
your range references in the with statement.

Sub AAA()

Sheets("Sheet1").Range("b3").FormulaArray = _
"=SUM(IF(sheet2!b$3:b$7=$A3,1,0))"
With Sheets("sheet1")
.Range("b3").AutoFill Destination:= _
.Range("b3:e3"), _
Type:=xlFillDefault
End With
End Sub
 
Neither of these suggestions have solved my problem. One more observation I
have made - the same macro that fails when it is attached to one worksheet
runs find when it is attached to another. It seems there is some sort of
scoping or control concept here that I am missing.

I'm less interested in making the symptoms disappear with code examples that
work but look equivalent to me than I am in understanding why what I have
done is not current.

Thanks again,

Don
 
Don,

Is your worksheet protected?

What happens if you do the same activities manually rather than via code?
Usually you get a more descriptive error message.

Rob
 
No it is not protected - if it was the initial statement that placed a
formula in a cell would have failed. I don't understand your concept of
"manually rather than code".
 
You can't select on any but the activesheet

An unqualified range refers to the activesheet.

Those are probably the reasons you are failing.

Most would look at what works and then look at what they have and see the
difference.
 
Don,

The line which fails:
Sheets("calculations").Range("b3").AutoFill Destination:=Range("b3:e3"),
Type:=xlFillDefault

Do the activity manually. ie. stop the code execution at that line, manually
fill from B3 to E3 yourself.
Excel will give you a better error message than VBA would.

Rob
 
Back
Top