Charting Empty Cells

[secoo140]

Hello
I provide some numbers via my excel userform to a sheet.
then I use that sheet to provide data to my charts.

Value of zero is a great problem. It makes my graphs meaningless.

how to solve this problem?

(userform pastes numbers to sheet)

 

[NdNoviceHlp]

Charts will skip N/A values, so if U adjust the range that your charting to replace zero with N/A then it should work...

=If(A1=0,"N/A",A1)

HTH. Dave

 

[secoo140]

But there is a problem.

My userform owerwrites the formula.
I use these cells to enter values from my userform

Private Sub submit1_Click()Dim ssheet1 As Worksheet
Set ssheet1 = ThisWorkbook.Sheets("Kanlar")


nr = ssheet1.Cells(Rows.count, 1).End(xlUp).Row + 1


ssheet1.Cells(nr, 1) = CDate(Me.DTpick1)
ssheet1.Cells(nr, 2) = CDec(Me.tbWBC)
ssheet1.Cells(nr, 3) = CDec(Me.tbHB)
ssheet1.Cells(nr, 4) = CDec(Me.tbPLT)
ssheet1.Cells(nr, 5) = CDec(Me.tbUREA)
ssheet1.Cells(nr, 6) = CDec(Me.tbKREA)
ssheet1.Cells(nr, 7) = CDec(Me.tbTBIL)
ssheet1.Cells(nr, 8) = CDec(Me.tbDBIL)
ssheet1.Cells(nr, 9) = CDec(Me.tbINR)
ssheet1.Cells(nr, 10) = CDec(Me.tbKALS)
ssheet1.Cells(nr, 11) = CDec(Me.tbALB)
ssheet1.Cells(nr, 12) = CDec(Me.tbNSE)
ssheet1.Cells(nr, 13) = CDec(Me.tbKROGA)
ssheet1.Cells(nr, 14) = CDec(Me.tbCEA)


Application.ScreenUpdating = True
Worksheets("Kanlar").Select

 

[NdNoviceHlp]

Maybe this bit of code addendum..

For cnt = 1 to 14
If ssheet1.Cells(nr, cnt) = 0 then
ssheet1.Cells(nr, cnt) = "N/A"
next cnt

Dave

 

[secoo140]

Maybe this bit of code addendum..

For cnt = 1 to 14
If ssheet1.Cells(nr, cnt) = 0 then
ssheet1.Cells(nr, cnt) = "N/A"
next cnt

Dave

Private Sub submit1_Click()Dim ssheet1 As Worksheet
Set ssheet1 = ThisWorkbook.Sheets("Kanlar")




nr = ssheet1.Cells(Rows.count, 1).End(xlUp).Row + 1




ssheet1.Cells(nr, 1) = CDate(Me.DTpick1)
ssheet1.Cells(nr, 2) = CDec(Me.tbWBC)
ssheet1.Cells(nr, 3) = CDec(Me.tbHB)
ssheet1.Cells(nr, 4) = CDec(Me.tbPLT)
ssheet1.Cells(nr, 5) = CDec(Me.tbUREA)
ssheet1.Cells(nr, 6) = CDec(Me.tbKREA)
ssheet1.Cells(nr, 7) = CDec(Me.tbTBIL)
ssheet1.Cells(nr, 8) = CDec(Me.tbDBIL)
ssheet1.Cells(nr, 9) = CDec(Me.tbINR)
ssheet1.Cells(nr, 10) = CDec(Me.tbKALS)
ssheet1.Cells(nr, 11) = CDec(Me.tbALB)
ssheet1.Cells(nr, 12) = CDec(Me.tbNSE)
ssheet1.Cells(nr, 13) = CDec(Me.tbKROGA)
ssheet1.Cells(nr, 14) = CDec(Me.tbCEA)


For cnt = 1 to 14
If ssheet1.Cells(nr, cnt) = 0 then
ssheet1.Cells(nr, cnt) = "N/A"
next cnt


Application.ScreenUpdating = True
Worksheets("Kanlar").Select

End Sub

not working (

 

[NdNoviceHlp]

The great Google seems to indicate that this line of code should be..

ssheet1.Cells(nr, cnt) = NA()

Maybe that will work? Dave

 

[secoo140]

Private Sub submit1_Click()Dim ssheet1 As Worksheet
Set ssheet1 = ThisWorkbook.Sheets("Kanlar")




nr = ssheet1.Cells(Rows.count, 1).End(xlUp).Row + 1




ssheet1.Cells(nr, 1) = CDate(Me.DTpick1)
ssheet1.Cells(nr, 2) = CDec(Me.tbWBC)
ssheet1.Cells(nr, 3) = CDec(Me.tbHB)
ssheet1.Cells(nr, 4) = CDec(Me.tbPLT)
ssheet1.Cells(nr, 5) = CDec(Me.tbUREA)
ssheet1.Cells(nr, 6) = CDec(Me.tbKREA)
ssheet1.Cells(nr, 7) = CDec(Me.tbTBIL)
ssheet1.Cells(nr, 8) = CDec(Me.tbDBIL)
ssheet1.Cells(nr, 9) = CDec(Me.tbINR)
ssheet1.Cells(nr, 10) = CDec(Me.tbKALS)
ssheet1.Cells(nr, 11) = CDec(Me.tbALB)
ssheet1.Cells(nr, 12) = CDec(Me.tbNSE)
ssheet1.Cells(nr, 13) = CDec(Me.tbKROGA)
ssheet1.Cells(nr, 14) = CDec(Me.tbCEA)




For nInputRow = 1 To 14
    If ssheet1.Cells(nr, nInputRow) = 0 Then
        ssheet1.Cells(nr, nInputRow) = "#YOK"
    End If
Next nInputRow




Application.ScreenUpdating = True
Worksheets("Kanlar").Select


End Sub

now it works.
we forgot to add "end if"

 

[secoo140]

I am trying to plot a chart that may sometimes contain blank values for the chart area. When I go to change the setting to "show gaps for empty cells", the option is grayed out. Why?

 

[NdNoviceHlp]

Well secoo140 I'm not sure about the "we" in #7 ? Maybe start a new post re. what's up with your previous post? Happy to have helped U out. Dave