|
Excel 2003 'SpecialCells' VBA cell Selection - how do I do this? Helpneeded please
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(xlCellTy peFormulas, 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(xlCellTy peFormulas, xlErrors).Rows(0,1).select thanks for any help Philip |
Excel 2003 'SpecialCells' VBA cell Selection - how do I do this? Help needed please
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 wrote in message ... 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(xlCellTy peFormulas, 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(xlCellTy peFormulas, xlErrors).Rows(0,1).select thanks for any help Philip |
Excel 2003 'SpecialCells' VBA cell Selection - how do I do this? Help needed please
Range("W:W").SpecialCells(xlCellTypeFormulas, _
xlErrors).Offset(0, -22).Select HTH, Bernie MS Excel MVP wrote in message ... 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(xlCellTy peFormulas, 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(xlCellTy peFormulas, xlErrors).Rows(0,1).select thanks for any help Philip |
Excel 2003 'SpecialCells' VBA cell Selection - how do I do this? Help needed please
Ignore this go with Bernie's !
- wasn't thinking ):- Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... 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 wrote in message ... 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(xlCellTy peFormulas, 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(xlCellTy peFormulas, xlErrors).Rows(0,1).select thanks for any help Philip |
Excel 2003 'SpecialCells' VBA cell Selection - how do I do this?Help needed please
On Apr 28, 6:00*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Range("W:W").SpecialCells(xlCellTypeFormulas, _ xlErrors).Offset(0, -22).Select HTH, Bernie MS Excel MVP wrote in message ... 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(xlCellTy peFormulas, 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(xlCellTy peFormulas, xlErrors).Rows(0,1).select thanks for any help Philip- Hide quoted text - - Show quoted text - Thanks - that's perfect ! Philip |
| All times are GMT +1. The time now is 10:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com