VBA to set cell value to #N/A

[beakerboy]

I'd like to set a cell's value to "#N/A" if another cell is #N/A. I was hoping the following would work, but using the =isNA() funtion on the result cell yields a false.

Unfortunately there is no Application.Worksheetfunction.NA()
Here's what I was using:

If (Application.WorksheetFunction.IsNA(myName.Item(current_row - 2)) = True) Then
    result_cell = "#N/A"

 

[DaleWatson123321]

beakerboy,

This example will place "#N/A" in the adjacent cell based on the current cell being "#N/A". I expect you'll be able to modify it to fit your needs.

Sub Test_NA()
If IsError(ActiveCell.Value) Then
ActiveCell.Offset(0, 1).Value = "#N/A"
End If
End Sub

Hope this helps.

Regards, Dale Watson

 

[beakerboy]

It would be great if I could use the ActiveCell functions, but I'm using this as a function and the cell containing the formula will not be the active cell.

here's my full code: I want the cell's value to be a #N/A if the loop exists without finding a number in the other range or if the other range contains an #N/A in the current row. Returning a string "#N/A" will place the string in the cell, but it doesn't evaluate in the isNA() function like typing #N/A into the cell.

I must be overlooking something.

Thanks,
Kevin Nowaczyk

Public Function myCopy(myName As Range, current_row As Integer)

If (Application.WorksheetFunction.IsNA(myName.Item(current_row - 2)) = True) Then
    myCopy = myName.Item(current_row - 2)
Else
    Dim i As Integer
    Dim stay_in_loop As Boolean
    stay_in_loop = True
    For i = 1 To current_row - 3
        If (stay_in_loop = True) Then
            If (Application.WorksheetFunction.IsNA(myName.Item(current_row - 2 - i)) = False) Then
                myCopy = myName.Item(current_row - 2 - i)
                stay_in_loop = False
            End If
        End If
    Next i
    If (stay_in_loop = True) Then
        myCopy = "#N/A"
    End If
End If
End Function

 

[TonyJollans]

Hi Kevin,

You don't want a string value of "#N/A", you want the actual #N/A value. Try this instead ..

[blue]:
:
If (stay_in_loop = True) Then
    myCopy = [=NA()]
End If
:
:[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[