Chart-problem.

[lode]

Hi,

I setup a chart with the following datasource :

Januari 5000 --->is coming from a formula
Februari 4500 --->is coming from a formula
Maart 5900 --->is coming from a formula
etc...

How can i change the values of the second column in VBA wihout changing them at the worksheet because the cells at the worksheet containing formulas.
In fact my problem is that the chart has to deny zero-values.

 

[Dreamboat]

Create another column that says =(second column cell), and format that one. If you need to, you can then hide the new column.

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[lode]

Thanks for responding.

I figured this out already but when I hide the new column, the chart becomes blank.

Best regards.

 

[Dreamboat]

I did not think that would happen.

If your chart's on it's own sheet, you can create another sheet with all of your data (just copy, go to new sheet, paste special-paste links), and base the chart on that sheet. Which you can hide, I just tested it.

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[lode]

I forgot something

In the new column i placed this :

=IF(C5=0;"";C5)
When C5 has a zero value then the cell in the new column becomes blank just as i wanted this BUT the chart gives me zero values instead of gaps.

Can you please help me out ??

 

[Dreamboat]

I have 2000. I'll try something out now.

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[lode]

Thank you very much, I also have 2000.
When i try the following code then the chart gives me gaps, so this works, but this is just the same as =IF(C5=0;"";C5)
I don't understand why the above doenst't work.

Private Sub Worksheet_Calculate()
For X = 5 To 16
If Cells(X, 3) = 0 Then '--->Original column
Cells(X, 12) = "" '--->New Column
Else
Cells(X, 12) = Cells(X, 3)
End If
Next
End Sub

Do you think this is a good solution, it works but....

 

[Dreamboat]

Yes, I do. Just read a technote from MS that tells you about the "" not equaling blank, but they don't give an alternative. I was looking for code...

Is there something you don't like about the solution--other than that it requires code?

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[Dreamboat]

Go to Tools-Options, Chart tab (with your chart selected), and take off "plot visible cells only". Then you can hide your column as I previously suggested...

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[Dreamboat]

Forget that, it won't work either, cause the cell still isn't blank. Sorry.

I'd go with the code if I were you. At least until tomorrow. Then, I can check with the guys at work.

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[lode]

Thanks again,

When I have a problem mostly i figure something out but every time I think there are a lot of people who can do this better.

Best regards.