473,395 Members | 2,795 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

Replacing #N/A with a zero

Hi there,

Does anybody know what's the best way to replace a #N/A with a zero when a value is not found?

The formula that I'm currently using is:-

=INDEX(Sheet4!C:C,MATCH(E21,Sheet4!A:A,0))

Thanks in advance!
Aug 7 '06 #1
2 2836
Banfa
9,065 Expert Mod 8TB
There is an EXCEL function ISNA that returns TRUE if it's input is #N/A so in general you could use

=IF( ISNA(expression), 0, expression)

It's a bit cumbersome though
Aug 7 '06 #2
Thanks for your reply Banfa.

I'm trying to do this another way by using the selection.replace command. But I can't get it to work as I need to look at the cell value and not the formula.

I'm using the code

ActiveSheet.Range("I:I").Select
Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, LookIn:=xlValues, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Every time I run the Macro it errors.

Anybody got any ideas?

Thanks,
Aug 8 '06 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: Martin | last post by:
I have a HTML parser that reads product pages from various retailers - and I want to optimize it somewhat: I download all HTML before I start the parsing - and to do that I want to: - Get rid...
13
by: yaipa | last post by:
What would be the common sense way of finding a binary pattern in a ..bin file, say some 200 bytes, and replacing it with an updated pattern of the same length at the same offset? Also, the...
1
by: Graham J | last post by:
Hello, Despite GoLive 6 still not wanting to acknowledge its existence, I have been using a 468x60 IFRAME for the single task of displaying advertising banners that are randomly generated by a...
7
by: Skybuck Flying | last post by:
Hi, if (__builtin_expect (!buffer->__init, 0)) { buffer->__a = 0x5deece66dull; buffer->__c = 0xb; buffer->__init = 1; } I am using visual c 6 and this code doesn't compile, it's probably...
35
by: jacob navia | last post by:
Hi guys! I like C because is fun. So, I wrote this function for the lcc-win32 standard library: strrepl. I thought that with so many "C heads" around, maybe we could improve it in a...
14
by: Adnan Siddiqi | last post by:
Hi Suppose I have following URLs comming from an HTML document <a href="http://mydomain1.com">Domain1</a> <a...
32
by: FireHead | last post by:
Hello C World & Fanatics I am trying replace fgets and provide a equavivalant function of BufferedInputReader::readLine. I am calling this readLine function as get_Stream. In the line 4 where...
1
by: patelgaurav85 | last post by:
Hi, I want to convert xml in one format into another xml format shown below Input xml : <Name> <Name1> <Name11>Name11</Name11> <Name12>Name12</Name12>
3
by: Blogh | last post by:
Hi, I''m using javascript to read and display values from and query where the results are returned in an XML format. One of the queries has no value returned at a certain node, something like:...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.