Solved: runtime error '1004' unable to set the formula property of the series class.

[gregj]

Hello all,

I am back and hope that we can fix this in pretty short order... I have a class starting pretty quick and need to get this working.

Attached is the experimental grade sheet that so many people here helped to create. My problem is that I expanded it to include a few more exams under each topic area. I now get a runtime error of 1004 every time. It says unable to set the formula property of the series class.

Can anyone PLEASE HELP?

[Bob Phillips]

Which cells on sheet1 are you trying to chart?

[gregj]

Which cells on sheet1 are you trying to chart?

I am trying to get B1 through BM 120

[Aussiebear]

Greg, I haven't had time to look at your chart issue, but I would like to make a suggestion to fix the "div by Zero" issue in column BN

In cell BN3 change the formula from

=Average(C3:BM3)

to

=IF(ISERROR(Average(C3:BM3)),0,Average(C3:BM3))

This will turn the result into zero until a result is entered into the range C3:BM3. Copy down the column to the last data row.

[Aussiebear]

Okay had a quick look, and made a couple of changes. Try this. If there's an issue, you'll have to ask the other guys as I'm off to bed as its getting close to the witching hour.... ( I don't want to see any of my relatives tonight).

[gregj]

I loaded the students names into the sheet, and still get the run-time error 1004. Could this be something germane to my computer? Either that or I am missing something essential here.

I need this chart to reflect each student's grade, throughout their tenure in Med School.

Additionally, I need to get the chart to outline at the horizontal axis, where the headers are included. for ex: Biochem has 7 exams under it as do all other subjects. If we can get the chart to reflect b1through 7 etc. etc.

As you can see, the chart as it stands now only goes up to H7, leaving off all DV 1-7, Phys 1-7 Psych 1-7 and N1-7

If I go into the vba script, I can see that it is leaving data off or at least it was at AL7, I inserted $BM$ to reflect all cells up to BM....

I would be happy to call anyone who might be able help and I can possibly solve this by walking through it on the phone? If you are interested in attempting this method.....email me a number where you can be reached and a good time and I will be more than happy to work this through with you. greg dot jensen at hotmail dot com

DESPERATE....

[gregj]

Aussiebear............Did.... thanks... that helps....

[Aussiebear]

In the copy, I posted, the chart data goes to 63 (nine subjects by 7 exams), and I fixed the div by zero error.... you post a different version, but tell me that what I supplied didn't work.

Please load your sudent names into the version I posted and then tell me what issues still remain.

[gregj]

In the copy, I posted, the chart data goes to 63 (nine subjects by 7 exams), and I fixed the div by zero error.... you post a different version, but tell me that what I supplied didn't work.

Please load your sudent names into the version I posted and then tell me what issues still remain.

I have added the student's as you asked..... when we add some "grades" then go to look at the charts, the runtime error occurs when we go backwards using the scroller.... if we have already gone forward. Additionally, not all of the courses show up on the bottom of the graph.....they stop on Dev Anatomy 36....Physiology, Psychiatry and neuro are not showing on the graph....I am uploading your copy with student names.... and some fake grades so you might see what I am talking about....

I really appreciate the help you are providing...

[Aussiebear]

I'm not sure why your versions never show the expanded charting data, but here is another upgraded version. In this version I have:
1. Expanded the chart data to include columns C to BN.
2. Realigned the Course labels to reflect which results they are associated with.
3. I have gone forward and backwards with the scroller approximately 10 times and I do not get a run time error. I do not know what might be causing this. Be advised that I am running this in 2007. Someone else here might like to check this issue.


Edited by Aussiebear: Please disregard this last post, as I've just become aware of another issue in the chart. I'll work on it tonight.

[gregj]

I'm not sure why your versions never show the expanded charting data, but here is another upgraded version. In this version I have:
1. Expanded the chart data to include columns C to BN.
2. Realigned the Course labels to reflect which results they are associated with.
3. I have gone forward and backwards with the scroller approximately 10 times and I do not get a run time error. I do not know what might be causing this. Be advised that I am running this in 2007. Someone else here might like to check this issue.


Edited by Aussiebear: Please disregard this last post, as I've just become aware of another issue in the chart. I'll work on it tonight.

Aussie, I thank you for your time.....I still get the run error and cannot see all the information. I am using 2003 version and that is then likely what it is? others please weigh in....if you read this...

[gregj]

Attached is the original chart we started with...back in the thread experimental grade sheet. It has been tinkered with considerably since then but kept getting a runtime error.....

I have come back to the original to show how I want the chart's horizontal axis to appear.... such that it mimics the cells in c2 through bm2. I can get it to appear like this for one person, but the second I scroll over to the next person it reverts back to the original setup where only numbers 1-63 appear.......

Furthermore if I use =randbetween(1,100) to generate random numbers to drive the chart, if I look to scroll back and forth between students I find I have to click on the graph to update it

I am uploading another version of this, Aussie bear uploaded his version and says that it works for him, no doubt that it does.... but for me in 2003, it doesn't... I am really perplexed now b/c this was running originally, and all that ever changed was an increase in the number of exams the profs wanted to give this year from 4 to 7.

I really do appreciate the help and I am learning a lot.... just not enough to figure this out... but am sure we will get to it, it isn't too far away from what I envisioned.

[Aussiebear]

Greg, my problem is that I've only been altering the chart data for the first record. Stay with what we've got as it far improved. I'm still working on your issue.

I believe it has to do with the charted area.

When you right click on the chart, and select chart area, it shows the charted area to be $C$3:$AL$3 for the first record. When I alter that to $C$3:$BM$3 it will show the correct number of subjects (and exams) for student 1. Scrolling to Student 2, the chart area returns to $C$4:$AL$4.

Rather than going to each of the students charted data and amending, I'm sure there's a simple method of being alter all of the data in one go. This is what I'm chasing.

[mdmackillop]

Give this a try
[vba]Sub ChartUpdate()
Dim Rng As String, Rw As Long


Rw = Range("M1").Value + 2
'Modify data range
Rng = "$C$1:$BM$" & Rw

ActiveSheet.ChartObjects("Chart 2").Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R" & Rw & "C3:R" & Rw & "C65"
ActiveChart.SeriesCollection(1).Name = Range("A3")

'Return activation to sheet
Range("A1").Activate
End Sub

[/vba]

[mdmackillop]

Alternative method. Consider moving relevant data to a preset chart data area. This may be simpler than manipulating chart code. It doesn't rule out using code to change the data area, if multiple entries are desired.
[vba]Sub ChartUpdate()
Dim Rng As String, Rw As Long
Rw = Range("M1").Value + 2
With Sheets("Sheet1")
.Range("A" & Rw).Resize(, 65).Copy .Range("A132")
End With
End Sub
[/vba]

[Aussiebear]

Thanks MD. I see you waited to see me struggle with this one.....

You didn't really take any offense with my old mother country remarks did you?

There you go Greg, Malcolm has not only fixed the issue but he's alo dressed it up in Top Hat and tails too.

[mdmackillop]

Thanks MD. I see you waited to see me struggle with this one.....

To be honest, I hadn't seen this question.
Struggling? I would call it "learning"!

Regards
Malcolm

PS
Mothers can be very patient with their prodigal offspring.

[gregj]

Thanks MD. I see you waited to see me struggle with this one.....

You didn't really take any offense with my old mother country remarks did you?

There you go Greg, Malcolm has not only fixed the issue but he's alo dressed it up in Top Hat and tails too.

No ****e... this appears to be perfect......I need to view it at work and load in the new students.
Was I correct with =randbetween (1,100)? This does generate a random set of numbers correct? I think this perhaps the best site on line for helpfulness and excel......

Thanks,

[Aussiebear]

Using the rand function is simply a distraction to the real issue, as the values continually change.

Thank you for the compliment regarding this site. The Admin staff work hard to encourage an open & frank exchange of ideas & knowledge. We all gain by this sharing. The fact that people like yourself publicily acknowlege this benefit is the "pat on the back" for the efforts we make. I thank you for that.