Charting Empty Cells

secoo140

Board Regular
Joined
Oct 12, 2013
Messages
85
Office Version
  1. 2010
Platform
  1. Windows
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)
3wxZCU.jpg


adLfde.jpg
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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...
Code:
=If(A1=0,"N/A",A1)
HTH. Dave
 
Upvote 0
But there is a problem.

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

r0LmMa.jpg


Code:
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
 
Upvote 0
Maybe this bit of code addendum..
Code:
For cnt = 1 to 14
If ssheet1.Cells(nr, cnt) = 0 then
ssheet1.Cells(nr, cnt) = "N/A"
next cnt
Dave
 
Upvote 0
Maybe this bit of code addendum..
Code:
For cnt = 1 to 14
If ssheet1.Cells(nr, cnt) = 0 then
ssheet1.Cells(nr, cnt) = "N/A"
next cnt
Dave


Code:
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 :((
 
Upvote 0
The great Google seems to indicate that this line of code should be..
Code:
ssheet1.Cells(nr, cnt) = NA()
Maybe that will work? Dave
 
Upvote 0
Code:
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"
 
Upvote 0
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?

large
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top