VBA if column B is not empty and not #NA return value in column D

Question

I want to add text to column D if column B in the same row is not empty and not giving #NA values.

This is my current code for not empty but i have no idea how to make it IF for both criteria

    Dim NoPK As Range
    Set NoPK = ThisWorkbook.Sheets("Existing").Range("D2:D" & Rows.Count).End(xlUp)
    
    For Each p In NoPK
        If p > 0 Then
        ThisWorkbook.Sheets("Existing").Range("M2:M" & Rows.Count).Value = "PK"
        End If
    Next p

Answer 1

• Range("D2:D" & Rows.Count).End(xlUp) doesn't locate the last data cell on col D. Please see the below code.

• Use And / Or in If clause for multiple conditions

Microsoft documentation:

Range.End property (Excel)

IsEmpty function

Sub demo()
    Dim NoPK As Range, p As Range
    With ThisWorkbook.Sheets("Existing")
        Set NoPK = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
        ' OR
'        Set NoPK = .Range("D2:D" & .Range("D" & .Rows.Count).End(xlUp).Row)
        For Each p In NoPK
            If Not (p.Text = "#N/A" Or IsEmpty(p.Value)) Then
                .Cells(p.Row, "M").Value = "PK"
            End If
        Next p
    End With
End Sub