# 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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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

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

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?

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

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
3
Views
38
Replies
3
Views
90
Replies
5
Views
232
Replies
2
Views
93
Replies
1
Views
257

### Forum statistics

1,203,224
Messages
6,054,241
Members
444,711
Latest member
Stupid Idiot ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back