Excel 2003 'SpecialCells' VBA cell Selection - how do I do this? Helpneeded please

[plivingstone]

Hi,

I have a piece of code that puts an error "NA()" in column W if the ID
in column A is not found on a reference worksheet ...

like this:
Range("W2:W" & Range("A1").End(xlDown).Row).Formula =
"=IF(COUNTIF(A:A,Recon_Activity!T:T)=0,NA(),"""")"

Normally, say I want to select the entire row of all the ones with an
error put in by the formula, I can use this:

Range("A2:W" &
Range("A1").End(xlDown).Row).SpecialCells(xlCellTypeFormulas,
xlErrors).EntireRow.Select

or I can in one step delete them all.

However, I would like to select the cells in column A where an error
value is found in column W ... much the same as above, but I would
instead of selectiing the entire row, how do I try and select the
cells in column A where there is an error on the same row in column
W...

I tried the below but it doesn't work:

Range("A2:W" &
Range("A1").End(xlDown).Row).SpecialCells(xlCellTypeFormulas,
xlErrors).Rows(0,1).select

thanks for any help
Philip

 

[Peter T]

Dim rng As Range, ar As Range
On Error Resume Next
Set rng = Range("A2:W" & Range("A1").End(xlDown).Row). _
SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow
On Error GoTo 0

If Not rng Is Nothing Then

For Each ar In rng.Areas
' for testing change colour
ar.Columns(1).Interior.ColorIndex = 6
'ar.Clear
' or
'ar.ClearContents
Next
Else
MsgBox "no cells found"
End If


Uncomment Clear or ClearContents to "delete" them, if that's all you want to
do. However if you need to build a new range of all the 'relative' cells in
col-a you could build a new range with Union of each 'ar'

Regards,
Peter T

 

[Bernie Deitrick]

Range("W:W").SpecialCells(xlCellTypeFormulas, _
xlErrors).Offset(0, -22).Select

HTH,
Bernie
MS Excel MVP

 

[Peter T]

Ignore this go with Bernie's !

- wasn't thinking ):-

Regards,
Peter T

 

[plivingstone]

Range("W:W").SpecialCells(xlCellTypeFormulas, _
xlErrors).Offset(0, -22).Select

HTH,
Bernie
MS Excel MVP















- Show quoted text -

Thanks - that's perfect !

Philip