MATCH and clear cells

[Inactiver183192]

HI

I am trying to do a simple match but get an error
Dim NAerror as variant


NAerror = Application.Match("#N/A", RawData.Range("A1:J1589"), 0)

I don't quite understand what is wrong the error comes up as error 2042 which is no match but the data is definitely there. I want to be able to find all cells and delete/clear them.THanks!

 

[circledchicken]

Hi,

Maybe try this alternative:

Sub Try()

Dim c As Range

For Each c In Range("A1:J1589")
    If IsError(c) Then ' Or Application.WorksheetFunction.IsNA(c)
        c.Clear 'or c.delete
    End If
Next c

End Sub

 

[AlphaFrog]

Or try something like this...

RawData.Range("A1:J1589").SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents

 

[Inactiver183192]

Hiya thanks for that. I will use it but I wanted to do a match just to make in quicker so that I don't have to look in each cell one by one rather just go straight to the matched cell.If anyone else has any help on the match function that would be great. I can't seem to get it to work on any level and I think it must be they way I define my variables or something. Cheers

 

[Inactiver183192]

I think I may have found the problem in match does the array just need to be 1 row or 1column array? I like your code alpha frog thanks alot for that!