Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VBA Question
Good morning everybody,
I noticed that periodically, code (specifically macro code in Excel) that was working flawlessly in the past suddenly starts to error out. For example, in making a line-graph, macro code will add data series to a chart and assign values but suddenly it draws an error when it hits code to name the data series. But if I move that line of code above the line in front of it and rerun the procedure that line works fine but then it errors out on the data series a couple lines down so I don't think the problem is the code. This is some example code. Charts.Add ActiveChart.ChartType = xlLine ActiveChart.SetSourceData Source:=Sheets("TRAD FEES EMPLOYEE STATS").Range( _ "B2"), PlotBy:=xlRows ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = _ "={""JAN"","" FEB"","" MAR"","" APR"","" MAY"","" JUN"","" JUL"","" AUG"","" SEP"","" OCT"","" NOV"","" DEC""}" ActiveChart.SeriesCollection(3).Name = "=""Riverside""" ActiveChart.SeriesCollection(4).Values = _ "='TRAD EXPENSES_EMPLOYEE_STATS'!R3C39:R3C50" ActiveChart.SeriesCollection(2).Name = "=""Corona""" ActiveChart.SeriesCollection(1).Values = "='TRAD EXPENSES_EMPLOYEE_STATS'!R3C2:R3C13" ActiveChart.SeriesCollection(1).Name = "=""Anaheim Hills""" ActiveChart.SeriesCollection(2).XValues = _ "={""JAN"","" FEB"","" MAR"","" APR"","" MAY"","" JUN"","" JUL"","" AUG"","" SEP"","" OCT"","" NOV"","" DEC""}" ActiveChart.SeriesCollection(4).Name = "=""Moreno Valley""" ActiveChart.SeriesCollection(2).Values = _ "='TRAD EXPENSES_EMPLOYEE_STATS'!R3C51:R3C62" ActiveChart.SeriesCollection(5).Name = "=""Yorba Linda""" ActiveChart.SeriesCollection(3).Values = _ "='TRAD EXPENSES_EMPLOYEE_STATS'!R3C27:R3C38" These data series were in sequential order mostly except when the errors started occuring I move some of them around to see if that effected the error so now they aren't in order. I currently get an error on line: ActiveChart.SeriesCollection(4).Name = "=""Moreno Valley""" but this error was occuring on this line before I moved this up a few lines: ActiveChart.SeriesCollection(2).Name = "=""Corona""" Why is this erroring out? I believe this is because of some sort of data corruption, but I am not sure what is causing the data corruptions, and is there any way to rebuild the flawed data or recompile so that I don't have to rewrite the entire thing or record a macro all over again to rebuild? Thanks. Cordially, |
#2
|
|||
|
|||
Recorded macros are notorious for working for awhile, then not at all. The macro recorder puts in a lot of extra junk and does not do a good job of writing clean code. Step through the code by putting the cursor on the first line
Sub SubName() then stepping through it by pressing F8. Hopefully by going through step by step, you will be able to see what each piece is doing, and then hopefully catching why the error occurs. What is the exact error? Quote:
__________________
Justin Labenne www.jlxl.net |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Rows w/formulas question | Excel Worksheet Functions | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Question about combining data from multiple workbooks into one rep | Excel Discussion (Misc queries) | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
conditional formatting question | Excel Worksheet Functions |