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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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
545
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
545
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
 

Forum statistics

Threads
1,141,740
Messages
5,708,233
Members
421,553
Latest member
Geeyj

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top