Hello folks,
I've been a long time fan of this website and have been able to solve a lot of Excel-related issues by browsing forums and reading articles here, to the point I'm now considered the magical excel wizard here in the office. If this was a radio station, I'd say "long time listener, first time caller."
I had a question regarding VBA, but it requires a bit of back story. I do quality checks on chemical compositions by plotting them on a line chart with a logaritmic y-axis. I use log scale because Chemical X could be 95% of the substance and you wouldn't see the other 15 chemicals, and the chemicals tend to form a pattern when plotted on log scale.
However, sometimes a chemical doesn't appear in a substance so it's 0%. If you try to put 0 on a log chart in Excel you get an error message: "Negative and zero values cannot be plotted on a log chart in Excel", or something similar. I know this because it's mathematically impossible, so I just add a formula to the pre-formatted template, and plot that instead, to make the error go away:
=IF(A1>0,A1,NA())
And bam, no more error. Which is all fine and dandy, except I've been tasked with creating a database of all the substances that have been examined at our laboratory in the last 5 years and there's thousands of the files, most of which have been created with the chart without the error-handling formula. I've already completed a VBA macro that goes through a list of folders and opens up all the excel files with pre-formatted templates in it and gets the info that it needs, but whenever this macro opens up a spreadsheet with one of these bad charts on it, the log chart error pops up again. So I have to babysit the macro all day and click on it whenever it shows up. Is there any way, in VBA, to handle this chart error when it pops up?
I've been a long time fan of this website and have been able to solve a lot of Excel-related issues by browsing forums and reading articles here, to the point I'm now considered the magical excel wizard here in the office. If this was a radio station, I'd say "long time listener, first time caller."
I had a question regarding VBA, but it requires a bit of back story. I do quality checks on chemical compositions by plotting them on a line chart with a logaritmic y-axis. I use log scale because Chemical X could be 95% of the substance and you wouldn't see the other 15 chemicals, and the chemicals tend to form a pattern when plotted on log scale.
However, sometimes a chemical doesn't appear in a substance so it's 0%. If you try to put 0 on a log chart in Excel you get an error message: "Negative and zero values cannot be plotted on a log chart in Excel", or something similar. I know this because it's mathematically impossible, so I just add a formula to the pre-formatted template, and plot that instead, to make the error go away:
=IF(A1>0,A1,NA())
And bam, no more error. Which is all fine and dandy, except I've been tasked with creating a database of all the substances that have been examined at our laboratory in the last 5 years and there's thousands of the files, most of which have been created with the chart without the error-handling formula. I've already completed a VBA macro that goes through a list of folders and opens up all the excel files with pre-formatted templates in it and gets the info that it needs, but whenever this macro opens up a spreadsheet with one of these bad charts on it, the log chart error pops up again. So I have to babysit the macro all day and click on it whenever it shows up. Is there any way, in VBA, to handle this chart error when it pops up?