Hide Zero Errors in Log Graph

dungyc

New Member
Joined
Oct 9, 2004
Messages
40
Hi,

I am a regular visitor to your forums and have enjoyed and benefitted from all of your shared knowledge.

However, I have a problem that I cannot track down the solution to on previous threads. It involves a Log-Log graph pre-formatted, prior to the data table being populated, which when empty displays a zero value error message. How can I stop this?

Any help or insight would be great,

thanks[/img]
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
This code will work for you.


Sub Clear_Zeros()

Range("B:B").Replace What:="0", Replacement:="", LookAt:=xlWhole


End Sub
 

dungyc

New Member
Joined
Oct 9, 2004
Messages
40
I apologise I didn't get my full meaning across.

The data used to plot the graph is the result of some quite complicated UDF's (in my simple Excel VBA experience). And therefore cannot be changed to "", as when they are initially zero or null, as it is used as an argument for another process.

All I want to do is suppress the following warning message flashing up:

"Negative or zero values cannot be plotted correctly on log charts. Only positive values can be interpreted on a logarithmic scale. To correct the problem, do one of the following:
i) Enter only positive values (greater than zero) in the cells to create the chart.
ii) In the chart, click the axis you want to change. On the format menu, click Selected Axis. Click the scale tab, and then clear the Logarithmic Scale check box."

I'm aware of both facts, but just need to suppress that message until the user has entered in the data to plot the chart.
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
It sounds to me like somewhere in the UDF

There is a line along the lines of

If Value < 0 Then
msgbox(...."Negative or zero values cannot be plotted ...")
End If



If you look at the code for the UDF and comment that If statemen out by putting a '
in front of that If statement, perhaps the code will still run. (just make sure you put a ' in front of all lines from If to End IF.


But not seeing the code, I don't know if that will affect the functioning of your code in other ways.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Have the UDF return #n/a! as in
Code:
Function myUDF()
    myUDF = CVErr(xlErrNA)
    End Function
dungyc said:
I apologise I didn't get my full meaning across.

The data used to plot the graph is the result of some quite complicated UDF's (in my simple Excel VBA experience). And therefore cannot be changed to "", as when they are initially zero or null, as it is used as an argument for another process.

All I want to do is suppress the following warning message flashing up:

"Negative or zero values cannot be plotted correctly on log charts. Only positive values can be interpreted on a logarithmic scale. To correct the problem, do one of the following:
i) Enter only positive values (greater than zero) in the cells to create the chart.
ii) In the chart, click the axis you want to change. On the format menu, click Selected Axis. Click the scale tab, and then clear the Logarithmic Scale check box."

I'm aware of both facts, but just need to suppress that message until the user has entered in the data to plot the chart.
 

dungyc

New Member
Joined
Oct 9, 2004
Messages
40
Thanks All,

Just for information:

I used a combination from the comments made. I changed the output to #NA. however that still not stop the message appearing. I experimented and changed the chart x axis to linear in order to see if there was a specific problem associated to one axis. I changed it back and tried a log log scale with none of the aforementioned problems! who knows!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

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
Top