Need help with simple VBA Script
 

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!

Need help with simple VBA Script
 

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!


.

Need help with simple VBA Script
 

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!


.

Need help with simple VBA Script
 

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!


.

Need help with simple VBA Script
 

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


"Donald Parker" wrote in message
...
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!


.

Need help with simple VBA Script
 

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".
"Rob van Gelder" wrote in message
...
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


"Donald Parker" wrote in message
...
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!


.

Need help with simple VBA Script
 

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.


--
Regards,
Tom Ogilvy

Donald Parker wrote in message
...
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!


.

Need help with simple VBA Script
 

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


"Donald Parker" wrote in message
...
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".
"Rob van Gelder" wrote in message
...
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


"Donald Parker" wrote in message
...
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!


.