| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Case selection in Excel 2003 | Excel Worksheet Functions | |||
| Copying Excel 2003 Selection into Outlook 2003 HTML E-Mail Message | Excel Discussion (Misc queries) | |||
| Excel 2003, Windows XP, Stuck in Cell Selection Mode | Excel Discussion (Misc queries) | |||
| Excel 2003 - Selection Tool Always ON | Excel Discussion (Misc queries) | |||
| Multi-selection problem in Excel XP and 2003 | Excel Discussion (Misc queries) | |||
Hybrid Mode
