Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple vb script to open, | Excel Discussion (Misc queries) | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Why won't this simple VBA script work in excel 2002? | Excel Discussion (Misc queries) | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Make it more simple or intuitive to do simple things | Charts and Charting in Excel |