Make the #N/A sign invisible

I've developed a simple program using Microsoft Excel and Visual Basic
Editor. In the worksheet, I have specified 10 cells according to their
own formula. All the cells contain #N/A sign as to show that the cells
contain their own formula ( I guess!)

After executing the program only 6 cells were automatically fill-in
with data. What can I do if I want the program to consider the other 4
cells as empty? (eventhough they actually have formulas)

This is because I want the program to automatically hide the empty
cells.(cells with no data) 

Anyone can help me? 
Thank you in advanced

0
fildzana (26)
10/17/2006 1:03:21 AM
excel 39879 articles. 2 followers. Follow

2 Replies
455 Views

Similar Articles

[PageSpeed] 24

On 16 Oct 2006 18:03:21 -0700, "rixanna" <fildzana@gmail.com> wrote:

>I've developed a simple program using Microsoft Excel and Visual Basic
>Editor. In the worksheet, I have specified 10 cells according to their
>own formula. All the cells contain #N/A sign as to show that the cells
>contain their own formula ( I guess!)
>
>After executing the program only 6 cells were automatically fill-in
>with data. What can I do if I want the program to consider the other 4
>cells as empty? (eventhough they actually have formulas)
>
>This is because I want the program to automatically hide the empty
>cells.(cells with no data) 
>
>Anyone can help me? 
>Thank you in advanced

You could use conditional formatting:

Select the cells
Format/Condtional Formatting
Formula Is:  =ISNA(cell_ref)
Format
  Font
  Color -- select same color as the cell background (e.g. white)


--ron
0
ronrosenfeld (3122)
10/17/2006 1:17:55 AM
Rixanna

The standard convention would be to wrap the formula to trap the error.  If 
it is always #N/A then you can use ISNA. e.g

=IF(ISNA('YourFormula'),"",'YourFormula')

This will return a 'blank' cell if the result of your formula is #N/A

The other functions in this error 'group' are

ISERR - handles all errors BAR #N/A
ISNA - handles #N/A errors
ISERROR - handles ALL errors

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
www.nickhodge.co.uk

"rixanna" <fildzana@gmail.com> wrote in message 
news:1161047001.317017.87830@i3g2000cwc.googlegroups.com...
> I've developed a simple program using Microsoft Excel and Visual Basic
> Editor. In the worksheet, I have specified 10 cells according to their
> own formula. All the cells contain #N/A sign as to show that the cells
> contain their own formula ( I guess!)
>
> After executing the program only 6 cells were automatically fill-in
> with data. What can I do if I want the program to consider the other 4
> cells as empty? (eventhough they actually have formulas)
>
> This is because I want the program to automatically hide the empty
> cells.(cells with no data)
>
> Anyone can help me?
> Thank you in advanced
> 


0
10/17/2006 6:24:22 AM
Reply:

Similar Artilces:

Advice on Making a TimeLine Control
Hello, I am in the process of making my first custom control, which needs to be a timeline that behaves similar to the timeline in MS Moviemaker (and other apps). So far I have created a CWnd and drawn the 'tick marks' at the top to represent time, and got it to successfully scroll horizontally and vertically. The complication is having to add a label area to the left, to label each entry in the timeline, because these labels must not scroll horizontally with the rest of the view. The tick marks at the top also must not scroll vertically with the rest of the view. I am not sure wha...

Can't Make Brochures
To make a brochure, I open up Publisher, go to 'Publications for Print' -> 'Brochures'. No matter which design I select, about 5 seconds after the 'Changing your Publication' window comes up, another window pops up telling me 'Publisher cannot complete the operation'. This only happens for brochures - all other publications work. Does anyone have the answer? Try a detect and repair in the Help menu. Do a disk clean-up. Don't delete the Office setup files if you do the disk clean-up. The clean-up is found by right clicking your hard drive, propertie...

"is not a valid name. Make sure
I just converted several my Access Databases to Access 2007 and I am getting the following error message on 2 of them: "is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. I have tried creating a new database in 2007 and importing the tables, queries, etc. I have tried turning the Auto Name Correction function off, I have made sure that there are no apsotrophe's in the path, but still can't seem to solve the issue. This only seems to happen on queries that link multiple tables. I have a que...

How can I make Excel 2007 addins available to all users
I teach at a university. We have the enterprise edition of Office 2007 installed in each of our labs. Each computer has Office installed on its own hard disk. It isn't housed on a separate server. The system is set up so that each student logs on the the machine with their own username. This seems to be creating some problems with Excel addins. 1) We need the Solver and Analysis ToolPaks available to all students essentially all the time without needing to turn them on by navigating the Office Button> Excel Options> Addins.... sequences. Our techs have tried a few things, but ...

How do you make a timeline?
In need of a timeline. How do I make one in publisher? WHat is the easiest way? Would another program be more help?? Use a table to make it up.. as in 'TABLE - INSERT' -- Mike Hall MVP - Windows Shell/User http://dts-l.org/goodpost.htm "lakehead" <lakehead@discussions.microsoft.com> wrote in message news:8100AA7E-8A77-4A66-9E47-32C39CE5DE4E@microsoft.com... > In need of a timeline. How do I make one in publisher? WHat is the easiest > way? Would another program be more help?? lakehead wrote: || In need of a timeline. How do I make one in publisher? ...

sign in failed
Is it normal to get a small window asking you to enter your email address and password for every hotmail account and with the following text at the bottom of the window, every time you start outlook? Sign-in failed. Outlook Connector has encountered an error. Please restart Outlook. If the issue continues, open the Control Panel, click Add/Remove Programs and repair Outlook Connector. If this does not fix the issue, contact Support. If there are issues with the account configuration; yes this could be normal. If it doesn't go away by itself in a few hours, check your firew...

How do I make a 2 sided brochure?
I am trying to make a 2 sided brochure. It will print landscape on 8 1/2 X 11 paper. Open one of the pre-designed brochures in Publisher to get an idea on how to setup a brochure. File, new. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "G. Mitton" <G. Mitton@discussions.microsoft.com> wrote in message news:5E9D207B-DA52-4C9B-A8D6-0683586E4431@microsoft.com... >I am trying to make a 2 sided brochure. It will print landscape on 8 1/2 X 11 > pape...

What makes a top video converter
How to 'get the best video converter' (http://tinyurl.com/ybt6yce)? First, you need to recognize what movie or audio format you have and what format you need to convert to. For example, if you want to put movie on your iPod, you need to convert the music to MP4. If you received video created on Mac, you probably want to look for something that can convert MOV to other windows media support, like WMV. 'top video converter' (http://www.topvideoconverter.com/) software should convert common formats like WMV, AVI, MP4 and MOV to similarly common formats. The top-notch mo...

Sign, encrypt and decrypt in a Citrix session
We use a Smart Card to; sign, encrypt and decrypt email in Outlook 2003. We also use the Smart Card for Domain authentication and for VPN authentication when the user is on the road with a notebook. Our organization has a Citrix server. You access the server via a https URL. It will then require you to point to your Smart Card email certificate and then enter the Smart Card PIN to access the company Citrix web site. The user can then click on an Outlook Icon and execute Outlook 2003 on the Citrix server. However, how can a person; sign, encrypt and decrypt email when using the Outlook ...

How Can I "sign" a spreadsheet to verify I reviewed?
...

How to make a formula bold based on imput
Hi i need some help, if anyone knows how to do this the help would b greatly appriciated. I have some formulas and need some help with/without conditiona formatting. Part 1 of the equation is Red Part 2 is Green if A1 <= 5 Then A1-A2 = Result if Result >1.0 Then print result in bold If 5 < A1 Then A1-A2 = Result If Result > (.20)(A1) Then print result in bold. is it possible to do this, what im trying to say is that if the firs part of the equation is true, and it is above 1. then print it in bold and ignore the bol statment for part 2 of the equation. If part 1 is false th...

making charts with boxes to write in
Is there a question? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Patsyspencer@sadie66762" <Patsyspencersadie66762@discussions.microsoft.com> wrote in message news:7CEFC048-A8E5-4D63-B050-7EAA723560AE@microsoft.com... > ...

howdo i make a chart showing grades for male and female students
Please help! Thanks! In column A enter grades for females and in Column B enter grades for males. A will be one series on your chart. B will be the other series on your chart. Gord Dibben MS Excel MVP On Mon, 3 Dec 2007 20:01:01 -0800, angeleyes <angeleyes@discussions.microsoft.com> wrote: >Please help! Thanks! ...

Making a Table
I need to be able to copy the cell values from multiple files, same cell locations, different file name into a master file buliding a master file table.... is there a fast way to edit the file name with out having to edit/find and replace the file name by manually typing the file name and resulting in the replacing of the file name... ...

how to make a drill down account summary report
Hello: We have the following situation: We are a major liquor distributor here in my country, we have several houses(the companies that internationally or regionally represents each brand and provides to us), and within every house there's a brand, for each brand we have it's expenses, and for each expense the detailed information of them. i.e: account_number Description 062 "Brand Expenses" 062101 "Globaliza" (This is the name of the house or company that sells to us) 062101--01 "Johnie Walker" (The n...

How to make formula grow with the table
I've created a table which will be updated periodically, at the top of the data (in other words, continually inserting a new Row 3). I want the formulae I insert to always adjust to reflect the new final row in ranges. How do I do that? Thanks in advance! Set up a dynamic range name as described below: http://www.contextures.com/xlNames01.html#Dynamic "Helen McClaine" wrote: > I've created a table which will be updated periodically, at the top of the > data (in other words, continually inserting a new Row 3). I want the > formulae I insert to always adj...

How to make excel not round real numbers when making a histogram?
When making a data histogram and say you have a range of numbers to be plotted such as 10.5,10.6,10.5,10 etc. and you define a bin range of 10,11,12,13 excel will take your real number valuse of 10.5,10.6etc and round them all to the nearst whole number and plot them under that range. This really messes up your histogram. Is there a way to make excel simply plot the real numbers in their respective ranges so in the example I gave how do you make the numbers all end up in the 10-11 range as they should. This is a really annoying problem I assume you could simply manual round all your va...

Making a field 'read only' after initial input
Is there a way to make a field input protected, after the initial entry? For example, once a new contact is created and they have selected a drop down value in the associate field, I want to have that field 'read only' once the association is selected. This field cannot be changed after it is selected? Any help would be much appreciated! Nora You can use an undocumented (and unsupported) metadata change. Look for your attribute and change its ValidForUpdateAPI to 0. You'll need to republish the forms to migrate the change. (You might need an IISRESET too, not sure on th...

Are there simple steps to make a template?
I typed in my question in the search box in Publisher but if I found the answer I don't know it. I just need (example): Go to Format, Click on Blah, Close box and open blah, blah. Can anyone help me? I'm attempting to make a template for a booklet. Help!!! Save as, files of type, expand to Publisher template. When you open Publisher, file, new, click templates. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "TerryLynn" <TerryLynn@discussions.microsoft.com> wrote in message news:28095ED1-4C9E-426E-87F3-4972...

Make columns cascade to the next sheet?
Is it possible to have columns cascade to the next sheet? Can column 1 on page one continue to column 1 of page two without going to column 2 of page one? On Sat, 6 Feb 2010 14:33:02 -0800, Slim Sr. <Slim Sr.@discussions.microsoft.com> wrote: >Is it possible to have columns cascade to the next sheet? Can column 1 on >page one continue to column 1 of page two without going to column 2 of page >one? No. If that's the kind of layout you need, use a two-column table. See http://www.shaunakelly.com/word/concepts/tables/index.html and http://www.word.mvps.org/...

Connect/disconnect from VPN makes Outlook 2007 hang forever
Outlook 2007, using Exchange. I launch OL 2007 and am connected to my cable internet. THEN I VPN to my client, and Outlook 2007 "hangs", displays an hourglass and never regains consciousness. I think once it regained consciousness after several minutes during the many times I have tried this. Other times it took so long that I terminated the app. This does not happen at all with Outlook 2003 (different machine, same configuration AFAIK). Outlook 2003 works perfectly and quickly. Alternatively, once I am connect to the VPN, THEN OPEN Outlook, it's OK (slow as hell, but ...

how do i make a brochure with 3 pages per sheet in publisher?
File, New, brochure, there are samples, select one and go from there... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "chris" <chris@discussions.microsoft.com> wrote in message news:41E8C29E-1ED4-4406-A634-15491287AACB@microsoft.com... > In addition to Mary's reply, the following link should help you get started on the design: http://office.microsoft.com/en-us/assistance/HA010563161033.aspx -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS"...

ToolStrip
Hello. I am using toolstrip. I want to make rectangle on the selected item only. How can I do that ? Thanks :) Problem solved. What I have found, that ToolStripItem, which is created by : ToolBox.items.add("a text" , myImage) is created as toolStripButton, which has checked component. I need only one button to be checked, so each time it is checked, I do a loop for the rest of the toolStripButtons. Thanks, anyway :) "Mr. X." <nospam@nospam_please.com> wrote in message news:OdicdxLCLHA.4388@TK2MSFTNGP04.phx.gbl... > Hello. > I am using t...

I cant sign in
After using Money for more than a year on the same computer - it suddenly doesnt allow me to sign in - any ideas? ...

Money 2004 Sign-in fails
Sihn-in fails even though name and password are correct. Been going on for the last two days. Reinsatll stil have the same problem. ...