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

[jenrique.cerda]

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.

 

[Cbrine]

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]

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:

=IF(ISNA(VLOOKUP(D13,Table,2,0)),"Yes",VLOOKUP(D13,Table,2,0))

 

[jenrique.cerda]

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]

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]

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:

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