Changing a cell´s value if actual value is #N/A

jenrique.cerda

Board Regular
Joined
Sep 22, 2006
Messages
53
Hi everyone,

I´ve been having trouble chaning a cell´s value when the cell´s value equals #N/A.

I have a column which has cells with formulas and some values returned by the formula is #N/A.

How can I change this value to "Yes" comparing all the cells in this column?

I´ve tried to do a for each cell in selection but I can´t seem to get it right. I´ve noticed that when I type "#N/A" my code changes this to "Yes" but not comparing the value "#N/A" returned by the formula.

Thanks.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
I'm not entirely sure what you are trying to do, but here's a quick hint that should help you out.

=If(ISNA(YourFormula),"Yes",YourFormula)

This structure around your formula will eliminate your #N/A errors.

HTH
Cal
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows
You can test for the "#N/A" return value in the formula, and return something other than the calculated value. The generic formula would be something like:
=if(isna({calculation}),{alternate value},{calculation})
this tests for whether the calculated result is "#N/A" - if so, it returns an alternate value, which could be another calculation (or form of calculation), an error message, or some other indicator; if not, it returns the intended calculation. In your example, if the intended calculation is to return a value from a lookup table, yoiu could use something like:
Code:
=IF(ISNA(VLOOKUP(D13,Table,2,0)),"Yes",VLOOKUP(D13,Table,2,0))
 

jenrique.cerda

Board Regular
Joined
Sep 22, 2006
Messages
53
I would like to change this "Yes" that was returned by the formula to another formula.... Is there a way to do this in VBA?

How can I look for this "Yes" value in VBA if it was returned by a formula?
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Are you using code to execute this process? If so, can you post the code? If you want to have a branching formula that does something different if the first formula errors, you don't need to use code at all??? Or are you doing other things as well?

Cal
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows
I would like to change this "Yes" that was returned by the formula to another formula.... Is there a way to do this in VBA?

You can do it in the formula itself, by inserting an alternate calculation to be returned if the result of the "primary calculation" is an error - there is no need to use VBA. If you are determined to use VBA, you can check for the "Yes" return value with something like:
Code:
sub CheckVal

dim cll as range

for each cll in selection
if cll.value = "Yes" then
   cll.formula = "=..." ' insert text of alternate formula
end if

end sub

One problem with this VBA solution is that the new formula will not (necessarily) have the same test for a "#N/A" result - if the operands change you may have the "wrong formula" in some cells. In this case, will have to either copy the formula that contains the test, or find out in some other way which cells need to be changed (back). I would recommend a formula approach, rather than VBA
 

Watch MrExcel Video

Forum statistics

Threads
1,109,537
Messages
5,529,426
Members
409,876
Latest member
Akash Yadav
Top