PDA

View Full Version : Line chart



Carina33
12-13-2006, 10:17 AM
Hi :hi:

I have a problem with a line chart. I have some data in one sheet that I use a macro for to copy to another sheet. I then have a line chart that I want to get updated using the data copied to the sheet. I don't want the chart to plot the empty cells but it seems that in a line chart the line will move to 0 if the next cell is empty.
I have this code to copy the data which does not distinguish if the cell is empty or not, it just copies the entire table (because I can't get that part to work...)


Sub Copiera()
Dim Source As Range, cel As Range
Dim Tgt As Range
Set Source = Sheets("Data").Range("B70:C84")
Sheets("Stat").Activate
Range("B32:C46").Select
Selection.ClearContents

Set Tgt = Sheets("Stat").Cells(47, 2).End(xlUp).Offset(1).Resize(, 2)
Source.Copy
Tgt.PasteSpecial Paste:=xlValues

End Sub

The actual line chart is located in the sheet where I have copied the data to but I have not recorded a macro for the chart because I am not sure of the code for this. I have just used the chartwizard to create the chart and was hoping this is good enough(?).

I have tried to go to Tools-Options-Chart-Plot empty cells as, but it does not help.

Is it possible to get the line in the chart to show only up until those cells that includes a value (i.e not the full range b32:C46)? In order to do this do I have to create the chart in the vba code window? I need the macro to copy the cells to the sheet which should display the chart because the values depends on what the user inputs each time and will hence vary.

I am sorry I can't provide any code for the chart but I am new to VBA so I don't know how to do this.

Thanks in advance

Carina

CodeMakr
12-13-2006, 11:41 AM
Will it work for you to only copy the cells to the new sheet if they are greater than zero??


Sub CopyNonZero()

Dim Tgt As Range, Src As Range, i As Long
For Each Tgt In Sheets(1).Range(Cells(1,1), Cells(Rows.Count, 1),End(xlUp)

Set Src = Sheets(1).Find(Tgt)

If Src > 0 Then
i = i + 1
Sheets(2).Cells(i, 1) = Tgt
End If
Next
End Sub

Carina33
12-13-2006, 12:24 PM
Hey CodeMkr

Thanks a lot for your suggestion. I do need to be able to copy 0 (zeros) values as well...

I have these formulas in the cells that are copied from the first sheet (to the sheet which displays the chart)

=IF(ISNUMBER(I11),I31,"")

When the cells are copied to the second sheet I only get the numbers, the formulas as not copied which is ok I think...

I have noticed one thing, that if I change the above formula to
IF(ISNUMBER(I17),C75+I37,NA())

Then I get, instead of a blank looking cell, #N/A in the cell. For #N/A values the line in my line chart appears to stops i.e it does not move to 0 as it does when I use the other formula with "" in it.

I don't think it looks ok to have this in the cells on the sheet that holds the chart but I guess I could accept it in the first sheet (it will be hidden eventually).

Could you adjust your code to only copy those cells that does Not say #N/A? Maybe then I can have the chart in the second sheet look right because it is linked to data in another sheet with #n/a values but the data next to the chart will look ok because the #n/a values will not have been copied.

Sorry if it is confusing but the stress is getting to me!

C

Carina33
12-13-2006, 12:27 PM
PS: it would be even better if the #n/a sign could be changed to say something nicer such as 'to be confirmed' but I am not sure this will work with the chart or how the code used for the copying would need to change. I know how to get it in a cell just use "not confirmed".

mdmackillop
12-13-2006, 01:24 PM
PS: it would be even better if the #n/a sign could be changed to say something nicer such as 'to be confirmed' but I am not sure this will work with the chart or how the code used for the copying would need to change. I know how to get it in a cell just use "not confirmed".
Have a look at the ISNA function, which combined with IF allows you to return your own text.

Carina33
12-13-2006, 03:21 PM
Thanks, I will try that.

Carina33
12-13-2006, 10:44 PM
Hi,

pleeeaaasseeee could someone tell me how to delete the #N/A values that results from the copying. This is the code that does not work:

Dim Source As Range, cel As Range
Dim Tgt As Range
Set Source = Sheets("Data").Range("B70:C84")
Sheets("Stat").Activate
Range("B32:C46").Select
Selection.ClearContents
Set Tgt = Sheets("Stat").Cells(47, 2).End(xlUp).Offset(1).Resize(, 2)
Source.Copy
Tgt.PasteSpecial Paste:=xlValues
With Sheets("Stat").Range("B32:C46").NullString = "=NA()"
.DisplayNullString = False
End With

End Sub


Very very grateful for a quick reply.

Thanks a ton in advance

C

JonPeltier
12-21-2006, 03:15 PM
I have noticed one thing, that if I change the above formula to
IF(ISNUMBER(I17),C75+I37,NA())

Then I get, instead of a blank looking cell, #N/A in the cell. For #N/A values the line in my line chart appears to stops i.e it does not move to 0 as it does when I use the other formula with "" in it.

I don't think it looks ok to have this in the cells on the sheet that holds the chart but I guess I could accept it in the first sheet (it will be hidden eventually).
Keep the NA() in the formula, but use conditional formatting to hide the #N/A error in the visible table:
http://contextures.com/xlCondFormat03.html#Errors
Or keep blanks in the display table, and use another table for the chart data. Formulas in the chart data link to the display data, and turn blanks into #N/A. Don't show the chart data to the user.

Cyberdude
12-21-2006, 03:36 PM
I use NA() a lot to trick the chart into ignoring a cell. As Jon says, the object is to use what works, but hide it from the user, and conditional formatting will do just that for you.

JonPeltier
12-21-2006, 03:42 PM
CF won't help if you need to perform calculations on the data. I find it's easier to use multiple data ranges, each linked to the original data, but one formatted for nice display, one (perhaps the original) left alone for calculations, and one set up for the needs of the chart, which are probably different than the needs of a human eyeball.