Trying to pick up an NA value in a cell when looping - can't get to work!

[highlystrung]

Dear all,

I'm trying to loop across row 2 (in my test file attached) and identify when the cell value is NA (as in cell J2). I want to identify it to be able to delete the column but am having trouble writing the correct code to do this (my latest attempts isattached as text and in the file). Can anybody point me in the right direction?

thanks,

neil

Dim WSD As Worksheet    'Object variables available throughout this module
Dim FinalCol As Single

Sub Macro3()
Dim StrTitle As String

    FinalCol = ActiveSheet.Cells(3, Application.Columns.Count).End(xlToLeft).Column

    For i = 1 To FinalCol
        Cells(2, i).Select
        StrTitle = ActiveCell.Value
       ' MsgBox StrTitle
        ActiveCell.Value = "= IF(ISNA(StrTitle),""blank"", StrTitle)"
       
    Next i
End Sub

Test File.xlsm

[Leith Ross]

Hello highlystrung,

It is easier and faster to use the Find method when searching for a cell value. Here is an example. This will change "#N/A" when found to "blank".

Sub Macro4()

    Dim Cell As Range
    Dim Rng As Range
    
        Set Rng = Cells(2, "A").EntireRow
        
        Set Cell = Rng.Find("#N/A", , xlValues, xlWhole, xlByColumns, xlNext, False)
        If Not Cell Is Nothing Then Cell.Value = "blank"
        
End Sub

[contaminated]

Hello

Maybe this one

Sub Test()
    Dim UsedColumns As Integer
    
    UsedColumns = Range("A1").CurrentRegion.Columns.Count
    
    For x = 1 To UsedColumns
        If WorksheetFunction.IsNA(Cells(2, x)) Or Cells(2, x) = "#N/A" Then Cells(2, x).Value = "Blank"
    Next x
End Sub

PS: didn't see Leith's code )))

[highlystrung]

Thanks guys, I'll try both, Neil