Clearing Cells which yield "#N/A"

[NicholasL]

Okay so I basically have a bunch of different cells and some of them yield "#N/A" the actual cell contains the formula "=RANK(AZ13,$AW13:$BI13,0)"

I have nesting loops to handle this. The first starts at the beginning of a row and checks each cell until it reaches the end of the row, and the next loop tells the program to go to the next row and keep going until all the rows have been checked.

I have tried telling it to get rid of the cells if the value = "#N/A" (as shown below)
I have tried to say "IsError(activecell) then..." but I eventually get an error that says "Type Mismatch"
It gets that error on a cell with formula -- "=RANK(AW29,$AW29:$BI29,0)" and whose value reads "#N/A" -- as far as I can tell it's exactly the same as the other cells which I clears the contents of fine.
I've also tried telling it to clear the content of the cell if the cell is not a number.
No luck so far.

Can anyone offer a tip or even explain why I get a type mismatch error on one cell but not another that is *seemingly* identical?


Sub eliminatingMISMATCH2()
Range("BR13").Select

Do
Do
If ActiveCell.Value = "#N/A" Then
ActiveCell.ClearContents
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.Offset(0, 1).Select
End If
Loop Until IsEmpty(ActiveCell)
ActiveCell.Offset(4, -13).Select

Loop Until ActiveCell = Range("BR181")

End Sub

[ragulduy]

You need Activecell.text = "#N/A" not activecell.value

[duncandhu]

Try:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe try:

Please Login or Register  to view this content.

[NicholasL]

Thanks for all the suggestions but for some reason that one cell in particular is still giving me the same Type Mismatch error. The strange thing is that if I copy the formula in that cell and put it in another cell (it doesn't reformat the formula to match the destination -- literally the same formula just in a different cell) it doesn't give me a type mismatch error. Clears the contents just fine.

Any thoughts as to why that might be?

[AB33]

Please Login or Register  to view this content.

[AB33]

More likely

Please Login or Register  to view this content.