#REF stuffing up my IF Statement

With the help of theses forums i've been able to create what I think is a masterpiece!

But when I run my macro and it returns #REF (which is a result im actually ok with) it throws up an error in my if statement.

So I run XXXXX, this brings across any standing instructions, then i change a value in any cell, say for example I7 i say "Yes" actioned.

the Worksheet_Change sub cracks the sads

I was thinking I might have to include ELSE statements, but im not sure how or if it will even work

Example attached: Corporate Action History Master.xlsm


Below is an extract where it errors

Any thoughts please

Please Login or Register  to view this content.

Hi

Why don't you change the NA() part of the formula to either be a blank ("") or, if you really want to have a non error result, then N/A. I think that would resolve the issue.

rylo

hmm trying both but still failing Error 2023

Rylo i actually used an adaptation of your formula (still using the other one for paydate) to to bring across the comment value

Please Login or Register  to view this content.

Hi

Try making the (n/a) into a string in the formula like so ""(n/a)""

rylo

argh that was so simple! thanks rylo