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

#### jenrique.cerda

##### Board Regular
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.

### 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
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)

HTH
Cal

#### dcardno

##### Well-known Member
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
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
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
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

Replies
6
Views
65
Replies
3
Views
119
Replies
3
Views
136
Replies
6
Views
64
Replies
2
Views
66