Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range Fails in VBA Code
Hello All,
I have a problem that's driving me nuts. I have a worksheet named 'Audit Data' layed out like this... Profit Center Period 712927 713927 716927 722927 01/2005 80.00% 80.00% 80.00% 80.00% 02/2005 03/2005 04/2005 05/2005 06/2005 Then the named range ('Audit_Data') has a formula like this..... =OFFSET('Audit Data'!A$2,0,0,COUNTA('Audit Data'!$A:$A),COUNTA('Audit Data'!$2:$2)) When I check the named range in the worksheet, it selects the area correctly. However, when I try to use the name range in VBA, I get an error 1004 (Method Range of Object failed). I have deleted the worksheet, thinking corruped sheet, and added a new one. I have changed the name of the worksheet and name range to a variety of different names and still the same error. But one thing I found even more puzzling, I changed the formula to do the same thing to a different worksheet of data, and the VBA worked. What am I missing? What tree am I not seeing in this forrest? Thank you in advance for any insight. Dean. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range Fails in VBA Code
Dean,
It worked okay for me. I typed this in the immediate window range("audit_data").Select and the range was selected fine. What is your code that uses this range? -- HTH RP (remove nothere from the email address if mailing direct) "Dean Hinson" wrote in message ... Hello All, I have a problem that's driving me nuts. I have a worksheet named 'Audit Data' layed out like this... Profit Center Period 712927 713927 716927 722927 01/2005 80.00% 80.00% 80.00% 80.00% 02/2005 03/2005 04/2005 05/2005 06/2005 Then the named range ('Audit_Data') has a formula like this..... =OFFSET('Audit Data'!A$2,0,0,COUNTA('Audit Data'!$A:$A),COUNTA('Audit Data'!$2:$2)) When I check the named range in the worksheet, it selects the area correctly. However, when I try to use the name range in VBA, I get an error 1004 (Method Range of Object failed). I have deleted the worksheet, thinking corruped sheet, and added a new one. I have changed the name of the worksheet and name range to a variety of different names and still the same error. But one thing I found even more puzzling, I changed the formula to do the same thing to a different worksheet of data, and the VBA worked. What am I missing? What tree am I not seeing in this forrest? Thank you in advance for any insight. Dean. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range Fails in VBA Code
Hey RP,
In order to determine my problem, I was using X=Application.Range("Audit_Data") to see if the range was good in VBA. However, I decided to create a copy of the named range (Named it "Audit_DataX") and point it to another worksheet like I did earlier. Then I added code Y=Application.Range("Audit_DataX") and tried the code again. Well, they both now work. So I am leaving it as is. One day I'll figure out what the glitch was, or maybe not. Thanks for the reply. I know this is not the proper solution, but I have to get this done tomorrow and I'm a little behind. Regards, Dean. "Bob Phillips" wrote: Dean, It worked okay for me. I typed this in the immediate window range("audit_data").Select and the range was selected fine. What is your code that uses this range? -- HTH RP (remove nothere from the email address if mailing direct) "Dean Hinson" wrote in message ... Hello All, I have a problem that's driving me nuts. I have a worksheet named 'Audit Data' layed out like this... Profit Center Period 712927 713927 716927 722927 01/2005 80.00% 80.00% 80.00% 80.00% 02/2005 03/2005 04/2005 05/2005 06/2005 Then the named range ('Audit_Data') has a formula like this..... =OFFSET('Audit Data'!A$2,0,0,COUNTA('Audit Data'!$A:$A),COUNTA('Audit Data'!$2:$2)) When I check the named range in the worksheet, it selects the area correctly. However, when I try to use the name range in VBA, I get an error 1004 (Method Range of Object failed). I have deleted the worksheet, thinking corruped sheet, and added a new one. I have changed the name of the worksheet and name range to a variety of different names and still the same error. But one thing I found even more puzzling, I changed the formula to do the same thing to a different worksheet of data, and the VBA worked. What am I missing? What tree am I not seeing in this forrest? Thank you in advance for any insight. Dean. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range Fails in VBA Code
Dean,
Did you previously define X as a range by any chance? If you did, the statement X= Range("Audit_Data") fails as objects need to be set Set X = Range("Audit_Data") If you subsequently left X and Y declared as no particular types, or even didn't declare them, as variants the statement X = Range("Audit_Data"0 would load an array X with all the values in the range. -- HTH RP (remove nothere from the email address if mailing direct) "Dean Hinson" wrote in message ... Hey RP, In order to determine my problem, I was using X=Application.Range("Audit_Data") to see if the range was good in VBA. However, I decided to create a copy of the named range (Named it "Audit_DataX") and point it to another worksheet like I did earlier. Then I added code Y=Application.Range("Audit_DataX") and tried the code again. Well, they both now work. So I am leaving it as is. One day I'll figure out what the glitch was, or maybe not. Thanks for the reply. I know this is not the proper solution, but I have to get this done tomorrow and I'm a little behind. Regards, Dean. "Bob Phillips" wrote: Dean, It worked okay for me. I typed this in the immediate window range("audit_data").Select and the range was selected fine. What is your code that uses this range? -- HTH RP (remove nothere from the email address if mailing direct) "Dean Hinson" wrote in message ... Hello All, I have a problem that's driving me nuts. I have a worksheet named 'Audit Data' layed out like this... Profit Center Period 712927 713927 716927 722927 01/2005 80.00% 80.00% 80.00% 80.00% 02/2005 03/2005 04/2005 05/2005 06/2005 Then the named range ('Audit_Data') has a formula like this..... =OFFSET('Audit Data'!A$2,0,0,COUNTA('Audit Data'!$A:$A),COUNTA('Audit Data'!$2:$2)) When I check the named range in the worksheet, it selects the area correctly. However, when I try to use the name range in VBA, I get an error 1004 (Method Range of Object failed). I have deleted the worksheet, thinking corruped sheet, and added a new one. I have changed the name of the worksheet and name range to a variety of different names and still the same error. But one thing I found even more puzzling, I changed the formula to do the same thing to a different worksheet of data, and the VBA worked. What am I missing? What tree am I not seeing in this forrest? Thank you in advance for any insight. Dean. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Wizard Fails to Recognize New Rows of a Named Range | Excel Worksheet Functions | |||
Code fails when sheet is protected | Excel Discussion (Misc queries) | |||
VBA code to affect value of a named range | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Logitech's newest mouse scroll fails in VBE code pane | Excel Programming |