Using an IF statement to delete a different cell?

[Gessen]

Hi Guys,

So say you have 3 cells. A, B, and C. I'm trying to come up with a function to put in C that erases/blanks A if B is #N/A. One I'm not sure how to do that and two I'm not sure if #N/A, which is the return from a VLookup that can't find anything, is a string or not. Can anbody help me out with this?

Thanks,

Gessen

 

[JoeMo]

You can't put a function in C that will erase the corresponding value in A based on the content of B. You can use VBA to this. Try this:

Sub EraseA()
Dim lRw As Long
lRw = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For Each c In ActiveSheet.Range("A1", "A" & lRw)
    If IsError(c.Offset(0, 1)) Then c.ClearContents
Next c
End Sub

 

[cb12]

If you didn't want to use VBA you could try something like this in A1

=IF(ERROR.TYPE(B1)=7,"","Insert original value of A1")

 

[cb12]

Apologies, was getting carried away, there won't always be an error. Perhaps?

=IF(ISNA(B1),"","Insert A1's original value")