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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

Peter Atherton wrote in message
...
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
-----Original Message-----
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!


.