Problem with VBA code and Range

I'm trying to create multiple charts via a macro in Excel 2007.
I'm getting a 1004 - Range of Object _Global Failed on the indicated statement. I believe I've done something wrong with the last Range in that statement, but I'm not sure what.

Sub OATChartCreate()

 Dim chtNew As Chart
 Dim i As Integer                                   '<<<
    Set chtNew = ActiveChart
    For i = 1 To 109                                '<<<
    Set chtNew = ActiveChart
>>>    chtNew.SetSourceData Source:=Range( _
        "'OAT Test Charts Data_Crosstab'!$F$1:$K$1, Range(ActiveCell,ActiveCell.Offset(0,6)).Select" _
        )    <<<
    chtNew.ChartType = xlColumnClustered
    chtNew.HasAxis(xlValue) = True
    chtNew.Axes(xlValue).MinimumScale = 0
    chtNew.Axes(xlValue).MaximumScale = 1
    chtNew.Axes(xlValue).MajorUnit = 0.1
    chtNew.Axes(xlValue).MajorUnit = 0.2
    chtNew.Axes(xlValue).TickLabels.NumberFormat = "0.00%"
    chtNew.Axes(xlValue).TickLabels.NumberFormat = "0%"
    chtNew.SetElement (msoElementPrimaryValueAxisTitleVertical)
    chtNew.SetElement (msoElementChartTitleAboveChart)
    chtNew.ChartTitle.Text = "Adams County - 8th Grade Mathematics Results"
    chtNew.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent Passing"
    Range("F2").Offset(i, 0).Select
    Next i
End Sub


jeffrmarks (10)
7/15/2011 11:36:45 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 43

"Jeffrey Marks" <> wrote:
> I'm getting a 1004 - Range of Object _Global Failed
> on the indicated statement.
>>>> chtNew.SetSourceData Source:=Range( _
> "'OAT Test Charts Data_Crosstab'!$F$1:$K$1,
> Range(ActiveCell,ActiveCell.Offset(0,6)).Select" _
>        )    <<<

Syntactically, it should be:

chtNew.SetSourceData Source:=Range( _
   "'OAT Test Charts Data_Crosstab'!$F$1:$K$1",

Note the change in where the right double-quote is.

PS:  I would also eliminate .Select in the second Range parameter.  I don't 
know if it hurts to have it, but it is not likely that it is helpful, much 
less necessary.

joeu20041 (141)
7/16/2011 5:04:14 PM

I wrote:
> "Jeffrey Marks" <> wrote:
>>>>> chtNew.SetSourceData Source:=Range( _
>> "'OAT Test Charts Data_Crosstab'!$F$1:$K$1,
>> Range(ActiveCell,ActiveCell.Offset(0,6)).Select" _
>>        )    <<<
> Syntactically, it should be:
> chtNew.SetSourceData Source:=Range( _
>   "'OAT Test Charts Data_Crosstab'!$F$1:$K$1",
>   Range(ActiveCell,ActiveCell.Offset(0,6)).Select)

Actually, even the latter form seems strange.  If ActiveCell is not in he 
'OAT Test Charts Data_Crosstab' worksheet, the specified range is erroneous. 
On the other hand, if ActiveCell is in the 'OAT Test Charts Data_Crosstab' 
worksheet, why use that qualifier for F1:K1?

Moreover, specifying Range(range1,range2) is dubious because that selects 
only the upper-left cell of range1 and the lower-right cell of range2.  So 
your statement is equivalent to:

chtNew.SetSourceData Source:=Range("F1",ActiveCell.Offset(0,6))

But I wonder if even that makes good sense.  Depends on your intent.

PS:  I finally did determine that .Select in that context causes a VBA 

joeu20041 (141)
7/17/2011 7:50:47 AM

Similar Artilces:

Averaging non-continuous ranges
Hi, I want to average a number of values that don't fall in one block, e.g. A1 A4 A7 etc. The problem being that there are more than 30 of them, and the Average function is limited to 30 individual cells (or ranges) for my version of Excel. Is there any way I can work around this? The cells will have a constant offset from each other, if that helps. -- Regards, Paul Hyett, Cheltenham There are two ways I can think of 1) Create a named range of the non-continuous range. In your formula put =AVERAGE(myNamedRange) and replace with your range 2) Create a User Defined F...

e2k3 smtp problem
We have a single w2k/ad domain with two exchange server in one exchange organization. We have recently been experiencing problem where the exchange smtp have been monopoliziing the internet bandwidth. There have been no pattern to when it happens but it occurrs a couple times a day for a period of 10-20 minutes. When this happens we shut down the default virtual smtp on the exchange servers and the internet returns to normal. We have a T1 and about 250 users mailboxes. We have checked and verify with MS technician that the exchange servers are not an open relay and we have McAfee ...

SendKeys problem with wdDialogInsertCrossReference
I have some code that works fine in Word 2003 for displaying notorious Insert Cross Reference dialog. This is to display the dialog so as to insert a cross reference to a Figure caption. With Dialogs(wdDialogInsertCrossReference) SendKeys "%t{home}ff" SendKeys "{enter}" SendKeys "%r" SendKeys "{down}" SendKeys "{enter}" SendKeys "%w" 'move to list of references rtn = .Display End With This code does not work in Word 2...

Problems with Ignore Other Applications
After setting the "Ignore Other Applications" checkbox on the General tab of Tools->Options, I get "Personal.xls locked for editing" when double-clicking to open another spreadsheet. After clicking on the Read Only button of this popup, the other spreadsheet correctly shows up. After I close these workbooks out without saving I try to open again with a double-click but get a popup which says it either can't find the spreadsheet itself or one of its components and after clicking OK I get no workbook showing at all. If I then close this and just open excel with a bl...

Problems with Aliases and Suppliers
One of our clients is running RMS 2 with the latest service packs and she has issues when modifying items in her database. Sometimes she will try to add an alias to an item but it will say the alias is already in use as an ILC or an alias for a different item. A quick query will show that it is in use, but for an item with itemid = 0. I have already deleted these aliases from the item but they keep getting re-added and the issue keeps reoccuring. Another issue she has is sometimes she will add a supplier to an item and then go to add it to a purchase order and it won't show up unde...

Passing a range from worksheet to VBA function
Hi all - I have the following function: Function regression(x, power()) For i = Lbound(power) to Ubound(power) regression = regression + x^i + power(i) Next End Function and wish to use the following in Excel =regression(A1, B1:B3) But I can't seem to get it to work. What am I missing? Thanks! Ryan On Nov 19, 12:46=A0pm, R Beck <R> wrote: > Hi all - > > I have the following function: > > Function regression(x, power()) > =A0 For i =3D Lbound(power) to Ubound(power) > =A0 =A0 =A0regression ...

Problem with SelectClipRegion
Hi all... I have this instructions in DrawItem virtual function of an owner drawn control. rgn.CreateRectRgn(rcLeft.left,, rcLeft.right, rcLeft.bottom); dc.SelectClipRgn(&rgn); dc.SetTextColor(RGB(255, 255, 255)); dc.DrawText(str, rcText, DT_CENTER | DT_VCENTER | DT_SINGLELINE); rgn.DeleteObject(); rgn.CreateRectRgn(rcRight.left,, rcRight.right, rcRight.bottom); dc.SelectClipRgn(&rgn); dc.SetTextColor(RGB(0,0,0)); dc.DrawText(str, rcText, DT_CENTER | DT_VCENTER | DT_SINGLELINE); rgn.DeleteObject(); dc.SelectClipRgn(NULL); rcLeft and rcRight are areas containe...

Outlook problems and then some....
OK, here's a sorry tale..... I was using Outlook 2000 very happily until last week. I have a lot of data and several PST files that I access from my Oultook Window. This is in order to keep the PST files below 1.2 Gig and it also helps me because each PST file relates to a seperate business (I work for several different businesses). I started to get memory errors popping up with a reference to the limits on the paging file. I increased the size of the paging file and defragged the drive to create more contiguos disk space. That didn't so it so I decided to take the plunge and upgrad...

Outlook 2003
Hi when I do a new message and go to click on the to: button for list of contacts its blank. then I can change address book... under the first contact.. there is another contact .. when I click on the second one my contact list shows up... now I also get a message to check outlook help so that I can delete the first contact (address book on list) but I can't figure it out how to delete it?? can anyone help. Thanks David Delete it here: Tools > E-mail accounts > View or change existing directories or address books > Outlook Address Book > Change. > -- Russ Valentine [...

Do You Have A SQL Query For This Problem?
Hello, I have a table in an Access database that I want to create an SQL Query for. Here is the table structure: Location Number Area1 2 Area1 3 Area1 5 Area2 4 Area2 2 Area3 1 I would like to create an update SQL statement so that it adds up the total of numbers for a particular location and puts that in the row. For example, for Area 1 the number would be 2 + 3 + 5, which equals 10. Here's the final result I would like: Location Number Area1 10 Area1 10 Area1 10 Area2 6 Area2 6 Area3 1 Does anyone have an SQL update ...

CTRL+1 and CTRL+ALT+T problems
When I use the CTRL+1 shortcut in Outlook 2007, it opens the mail view which is good. Problem is, it selects the default Inbox folder that I don't use. I have to scroll down the list of folders to get to my actual Inboxes. Is there a way to specify which Inbox Outlook selects when using the CTRL+1 command? What if I was to just delete the empty default Inbox that it always lands on? My second question is, why doesn't Outlook 2007 bring up a new task window when typing the CTRL+ALT+T command? I was told this should work even if you're in a different program or ...

Caching problems
I'm running Outlook 2003 aagainst an exchange server using Cached Mode. I had a user that had one email alias but it was changed to a different name. I did this yesterday. I still see his alias showing up as the old alias. I've sync'd my addressbook and updated the RUS on the server but when I go to resolve the name in the client I STILL get the old alias. Is there a way to clear the cache on the client? What can I do to get the correct alias? THanks in Advance! CT ...

Three problems with W2007; need help.
So far Word 2007 has been a problem for almost every W2003 doc I have opened. I am trying to keep an open mind with this but working with advanced knowledge of W2003 is almost useless. 1. The changed text screentips balloons are now not removable if you want to see the changes. 2. Graphic objects move in almost random ways when you try to use arrow keys to relocate them. 3. Record a paste unformatted text macro and the result will NOT past unformatted text; it pastes formatted text. Anyone got any ideas on any of these? Best of Luck 3. Add the Paste Unformatted comm...

Append Query problem 01-30-08
I am attempting to create an Append query involving three tables but am having trouble. This is what I'm trying to do. Table A has a field called "Email", as does Table B. I start the query as a SELECT query and in the grid I add the email field from Table A and set the criteria to match the email field in Table B (these two tables are not already directly related by the way). This part works correctly. It only selects the email field in Table A that matches a corresponding email in Table B. Table A is already related to Table C by a field I'll call "StudentID" (...

MS Money 2007 automatic software update problems
I am having the automatic update problem with Money 2007, in which every time I start it up, it prompts me for the update even though it has already been through it and supposedly "successfully" updated. I found the post suggesting uninstalling IE7, and I tried that, but to no avail. I wonder if anyone has any other suggestions. I found my update log, and will post it here: Date: 07/28/2007 (mm/dd/yyyy) Time: 02:31:56 (hh:mm:ss) Entering mnyupdate.exe Parsing cmdline ParseCmdLine: CmdLine = "C:\Program Files\Microsoft Money 2007\mnyupdate!@#@.exe" /p:0x688 /a:"C:\P...

Filtering records using VBA
How can I filter a recordset used in a form record by record programatically rather than via SQL? I have a fairly complex vba function that can decide whether the record needs to be displayed or not in the form but it's difficult/ impossible to convert into an SQL statement. Is there any way I can do this? wrote: >How can I filter a recordset used in a form record by record >programatically rather than via SQL? > >I have a fairly complex vba function that can decide whether the >record needs to be displayed or not in the form but it's diffic...

Electronic Reconcile
Hello, the limitations in the transaction code types have been posted before; what I am wondering about is "workarounds". We have multiple transaction types for withdrawls (Check Paid, Preauthorized Debit, Outgoing Money Transfer, etc). The same with deposits. Is there a way to map multiple codes to the same Type? While not ideal, I would rather have the multiple withdrawls come through as check paid than error out. If it is not possible (doesn't look like it is), any other suggestions? Thanks in advance, Jason Jason, Out of the box, no. You get one code per type. ...

displaying old Macro code
I am using a Macro program written in Excel. It was originally written in Excel 4.0 macro code and eventually ported into VBA. The developer kept both versions on the code and saved them in .XLW files. I need to edit the old code to disable it but cannot do anything but run it. The .XLW files contain an XLS sheet and a XLM macro spreadsheet and some VBA code. I can see the sheet and the VBA code but I cannot display the old Macro Code. I can see the macros listed in the Macro window but pressing Edit does nothing. Is there a module I need to install? If I crash a portion of the p...

Formula Problem #13
I seem to have trouble w/ a particular formula. I am attempting to round a number to the nearest tenth. No big deal...simple stuff if it were to stop there. I have rounded the number which returns as a decimal. I don't have trouble with any other number except when dealing with a zero in the 10ths spot. When I add text to the formula the decimal is not shown. Is there a way to have Excel first determine whether I have a zero in the tenths column and if so, have it show the decimal? Here's an example of the formula... FORMULA RESULT ...

Lookup Codes
We are having some difficulties determinig a strategy for lookup codes. Specfically, we are trying to determine the best way to configure lookup codes for scalability, ease of data entry, as well ease of use at cash out. The customization for my POS system is a clothing boutique. We have scattered inventory, different venders from season to season, and limited stock for the items we do carry. I really wondering if its better to simply make lookup codes numeric, and print them out on our price tags. Is there some better strategy or approach to handling lookup codes? This is a multi...

Guys, I hate to be pushy but I have a situation here. I have no time to read telephone-book sized tech manuals, trial-and-error is getting me nowhere, and I need to produce results FAST! What I need is a working sample of code illustrating the use of the qsRules.Dll within ASP.Net (and presumably either VBscript or Javascript). I know it's possible. I just need an example to get me started. I am close to getting VB to work; however, getting an error that the object can't be created. I am also interested in just the steps to get an object to work in .Net. Look up creati...

VB Code For Taking a user to a named cell
Hi, I have a long table of contents for a large excel model. I'd like to have a button next to certain items, that when the user clicks the button, Excel will take the user to that named Cell. Can anyone tell me what VB code would go in a command button to allow me to do this? Any help would be huge, thank you. Brady Brady, You can do this using Insert - Hyperlink. Place in this document. No code required for this solution. -- Earl Kiosterud ----------------------------------------------------------------------- "Brady Finney" <btf@atlantic-mgm...

c>c++ code translation
Can someone explain what this class could be doing I don't know C++. Thanks void CRequest::OnSpawn() { CString str; SelectTransactionProcessor(""); // Selects default transaction processor srand((unsigned)time(NULL)); for (int i=0; i<47; i++,rand()) {}; KeyL=(rand()<<16)+rand(); KeyM=(rand()<<16)+rand(); str.Format("220 %08x%08x%04x%04x%04x%04x%04x%04x%04x%04x\r\n",KeyL,KeyM,rand(),rand(),rand(),rand(),rand(),rand(),rand(),rand()); (*this) << str.GetBuffer(0); TicksTillDisconnect=600; } "segue" <

Recognize which form is calling the code
Hi - I have a contacts form that can be used to enter a new contact into the database. I have many forms throughout the system that may call this form at any time. I am writing some code and I want to save the name of the form that originally called the contacts form in a variable so I can pass control back to the original form. Can anyone help me with this? Thanks, Elizabeth ' Some other Form… Private Sub cmdOpenContacts_Click() DoCmd.OpenForm "Contacts", OpenArgs:=Me.Name End Sub ' In Form Contacts… Private Sub Form_Open(Cancel As I...

Unusual Printing Problem Outlook 2K
I have an unusual printing problem that started a few days and have not been able to find the problem. This problem not only effects my Office 2000 but also Explorer 6.0 (with SP1). Now for the problem... When printing from both Outlook 2k and Explorer the computer will take each page that is to be printed - split it into 3 equal parts, center them on the page it is printing on and print it. In other words each 1 page to be printed takes 3 pages to print it all. This problem only effects printing from Outlook & Explorer. The printer works fine when printing Word docs, DOS doc, Quicke...