Excel VBA Chart Not Recognizing Values

Question

I have another pretty perplexing problem that one of you might be able to solve. So basically I have this code to write a line graph using Excel VBA. It's made so it can be dynamic and can change if more values are added. But it's reading the first value as 0 and the last value as 0 even though they are not 0. Here is the code I'm using to put the graph on the page.

Sub createchart4()

lastA = Range("A1").End(xlDown).Row
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Range("Main!$A$1:$A$" & lastA)
ActiveChart.ChartTitle.Select
ActiveChart.SeriesCollection(1).Name = "=Main!$A$1"
ActiveChart.SeriesCollection(1).Values = "=Main!$A$3:$A$" & lastA

End Sub

It's saying that A3's value is 0 when it's actually 1534 and it's also reading A10 and A11 as 0 also when they're different as well.

If you can figure this out, I would be amazed :D

Siddharth Rout · Accepted Answer · 2012-05-31 04:03:19Z

3

The only scenario when a Chart ignores a number is when the number is stored as text. See Snapshot

enter image description here

How to know if the number is stored as text and how do we rectify it?

If you are using Excel 2003 then go to menu Tools | Options. Under the Error Checking Tab, ensure that the checkbox Number Stored as text is checked.

In Excel 2007/2010, click on Excel Options which can be accessed from the Office Icon in Excel 2007 or Options in the File Tab in Excel 2010.

Go to the Formulas tab and ensure that Numbers formatted as text or preceded by an apostrophe is checked.

If the above checkbox is checked then you will notice a small Green Triangle as shown in the snapshot above. Select the entire range starting from the 1st cell which has the Green Triangle. Click on the Exclamation mark. Click on Convert to number and you are done.

enter image description here

answered May 31, 2012 at 4:03

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EbilGenius Over a year ago

Thank you for your indepth and thought out response, and on my last question xD. Just an update, even though this was a brilliant idea, it was not the problem. I made a module that would take all the values and make them equal to itself. Silly I know, but is was the only thing that worked. Thank You!!

Jon Peltier Over a year ago

Where did the data come from? Was it imported from womewhere outside of Excel? Before you ran this little macro, were the cells formatted as text? Were the contents left-justified like text or right-justified like numbers?

Collectives™ on Stack Overflow

Excel VBA Chart Not Recognizing Values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related