Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Selection.SpecialCells for cells with a particular value?
Hello,
Im looking for a way to select all cells in a current region with a certain value. Similar to what Go To Special does with blanks or constants. Selection.SpecialCells(xlCellTypeConstants, 23).Select Id like to avoid using loops or If then, because the region is quite large and there are few cells that meet the condition. The value is the word test. Is there a way to use Selection.SpecialCells for cells containing the word test? After all the cells containing the word test are selected, Ill change the selections format. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Selection.SpecialCells for cells with a particular value?
Post some sample data with several that do contain "test" -- Don Guillett Microsoft MVP Excel SalesAid Software "h2fcell" wrote in message ... Hello, Im looking for a way to select all cells in a current region with a certain value. Similar to what Go To Special does with blanks or constants. Selection.SpecialCells(xlCellTypeConstants, 23).Select Id like to avoid using loops or If then, because the region is quite large and there are few cells that meet the condition. The value is the word test. Is there a way to use Selection.SpecialCells for cells containing the word test? After all the cells containing the word test are selected, Ill change the selections format. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Selection.SpecialCells for cells with a particular value?
Below is a small sample:
ColA ColB ColC ColD ColE ColF ColG 2 John Act test Temp Exp 3 Jill Act Temp Full Act 4 Tom test test Full 5 Jerry Exp 6 Kevin test test 7 Alison Exp 8 Julie test Full test test 9 Andrew 10 Dan Act test Temp Exp 11 Steve 12 Bill test Temp test test 13 James 14 Lisa Act test Full Temp "Don Guillett" wrote: Post some sample data with several that do contain "test" -- Don Guillett Microsoft MVP Excel SalesAid Software "h2fcell" wrote in message ... Hello, Im looking for a way to select all cells in a current region with a certain value. Similar to what Go To Special does with blanks or constants. Selection.SpecialCells(xlCellTypeConstants, 23).Select Id like to avoid using loops or If then, because the region is quite large and there are few cells that meet the condition. The value is the word test. Is there a way to use Selection.SpecialCells for cells containing the word test? After all the cells containing the word test are selected, Ill change the selections format. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Selection.SpecialCells for cells with a particular value?
Nope.
You may want to try using range.find and a loop--look at VBA's help for .find. You may find that it's pretty quick. Another alternative--if you don't have any errors in that selected range: Option Explicit Sub testme() Dim CurWks As Worksheet Dim TempWks As Worksheet Dim TestRng As Range Dim mySel As Range Dim myArea As Range Dim myWord As String myWord = "Test" Set CurWks = ActiveSheet Set mySel = Selection Set TestRng = Nothing On Error Resume Next Set TestRng = mySel.Cells.SpecialCells(xlCellTypeConstants, xlErrors) On Error GoTo 0 If TestRng Is Nothing Then 'keep looking Else MsgBox "Errors in Constants in this range" Exit Sub End If Set TestRng = Nothing On Error Resume Next Set TestRng = mySel.Cells.SpecialCells(xlCellTypeFormulas, xlErrors) On Error GoTo 0 If TestRng Is Nothing Then 'keep looking Else MsgBox "Errors in Formulas in this range" Exit Sub End If Application.ScreenUpdating = False Set TempWks = Worksheets.Add For Each myArea In mySel.Areas myArea.Copy TempWks.Range(myArea.Cells(1).Address).PasteSpecia l Paste:=xlPasteValues Next myArea TempWks.Cells.Replace what:="*" & myWord & "*", replacement:="#N/A", _ lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False Set TestRng = Nothing On Error Resume Next Set TestRng = TempWks.Cells.SpecialCells(xlCellTypeConstants, xlErrors) On Error GoTo 0 If TestRng Is Nothing Then MsgBox "No cells with: " & myWord & " in it" Else Set TestRng = CurWks.Range(TestRng.Address) End If Application.DisplayAlerts = False TempWks.Delete Application.DisplayAlerts = True If TestRng Is Nothing Then 'do nothing Else Application.Goto TestRng End If Application.ScreenUpdating = True End Sub h2fcell wrote: Hello, Im looking for a way to select all cells in a current region with a certain value. Similar to what Go To Special does with blanks or constants. Selection.SpecialCells(xlCellTypeConstants, 23).Select Id like to avoid using loops or If then, because the region is quite large and there are few cells that meet the condition. The value is the word test. Is there a way to use Selection.SpecialCells for cells containing the word test? After all the cells containing the word test are selected, Ill change the selections format. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Selection.SpecialCells for cells with a particular value?
Dave,
On a previous question, you recommended replacing the desired value with errors then using Selection.SpecialCells(xlCellTypeFormulas, 16).Select to select the desired cells with errors. This works for me since I dont normally have errors in the region. Below is the code Im using. Cells.Replace What:="test", Replacement:="=NA()", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.SpecialCells(xlCellTypeFormulas, 16).Select Selection.Delete Shift:=xlUp Range("A1").Select Beats looping through cells or using If then. Thanks. "Dave Peterson" wrote: Nope. You may want to try using range.find and a loop--look at VBA's help for .find. You may find that it's pretty quick. Another alternative--if you don't have any errors in that selected range: Option Explicit Sub testme() Dim CurWks As Worksheet Dim TempWks As Worksheet Dim TestRng As Range Dim mySel As Range Dim myArea As Range Dim myWord As String myWord = "Test" Set CurWks = ActiveSheet Set mySel = Selection Set TestRng = Nothing On Error Resume Next Set TestRng = mySel.Cells.SpecialCells(xlCellTypeConstants, xlErrors) On Error GoTo 0 If TestRng Is Nothing Then 'keep looking Else MsgBox "Errors in Constants in this range" Exit Sub End If Set TestRng = Nothing On Error Resume Next Set TestRng = mySel.Cells.SpecialCells(xlCellTypeFormulas, xlErrors) On Error GoTo 0 If TestRng Is Nothing Then 'keep looking Else MsgBox "Errors in Formulas in this range" Exit Sub End If Application.ScreenUpdating = False Set TempWks = Worksheets.Add For Each myArea In mySel.Areas myArea.Copy TempWks.Range(myArea.Cells(1).Address).PasteSpecia l Paste:=xlPasteValues Next myArea TempWks.Cells.Replace what:="*" & myWord & "*", replacement:="#N/A", _ lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False Set TestRng = Nothing On Error Resume Next Set TestRng = TempWks.Cells.SpecialCells(xlCellTypeConstants, xlErrors) On Error GoTo 0 If TestRng Is Nothing Then MsgBox "No cells with: " & myWord & " in it" Else Set TestRng = CurWks.Range(TestRng.Address) End If Application.DisplayAlerts = False TempWks.Delete Application.DisplayAlerts = True If TestRng Is Nothing Then 'do nothing Else Application.Goto TestRng End If Application.ScreenUpdating = True End Sub h2fcell wrote: Hello, Iâm looking for a way to select all cells in a current region with a certain value. Similar to what Go To Special does with blanks or constants. Selection.SpecialCells(xlCellTypeConstants, 23).Select Iâd like to avoid using loops or If then, because the region is quite large and there are few cells that meet the condition. The value is the word âœtestâ. Is there a way to use Selection.SpecialCells for cells containing the word test? After all the cells containing the word test are selected, Iâll change the selections format. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Selection.SpecialCells for cells with a particular value?
Hi,
I read the last line of your message, maybe you don't need to select the cells with test, why not apply conditional formatting to the entire range and format only. Select the entire range and choose Format, Conditional Formatting and type test into the second box and click the Format button. If one of these formats is what you intend than this will solve your problem. In 2007 you can also apply Number Formats via conditional formatting but not in 2003. -- Thanks, Shane Devenshire "h2fcell" wrote: Hello, Im looking for a way to select all cells in a current region with a certain value. Similar to what Go To Special does with blanks or constants. Selection.SpecialCells(xlCellTypeConstants, 23).Select Id like to avoid using loops or If then, because the region is quite large and there are few cells that meet the condition. The value is the word test. Is there a way to use Selection.SpecialCells for cells containing the word test? After all the cells containing the word test are selected, Ill change the selections format. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Selection.SpecialCells for cells with a particular value?
I didn't realize that you were going to delete the cells when you were done. I
thought you were only looking for a way to select them. That's why the more involved code this time. h2fcell wrote: Dave, On a previous question, you recommended replacing the desired value with errors then using Selection.SpecialCells(xlCellTypeFormulas, 16).Select to select the desired cells with errors. This works for me since I dont normally have errors in the region. Below is the code Im using. Cells.Replace What:="test", Replacement:="=NA()", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.SpecialCells(xlCellTypeFormulas, 16).Select Selection.Delete Shift:=xlUp Range("A1").Select Beats looping through cells or using If then. Thanks. "Dave Peterson" wrote: Nope. You may want to try using range.find and a loop--look at VBA's help for .find. You may find that it's pretty quick. Another alternative--if you don't have any errors in that selected range: Option Explicit Sub testme() Dim CurWks As Worksheet Dim TempWks As Worksheet Dim TestRng As Range Dim mySel As Range Dim myArea As Range Dim myWord As String myWord = "Test" Set CurWks = ActiveSheet Set mySel = Selection Set TestRng = Nothing On Error Resume Next Set TestRng = mySel.Cells.SpecialCells(xlCellTypeConstants, xlErrors) On Error GoTo 0 If TestRng Is Nothing Then 'keep looking Else MsgBox "Errors in Constants in this range" Exit Sub End If Set TestRng = Nothing On Error Resume Next Set TestRng = mySel.Cells.SpecialCells(xlCellTypeFormulas, xlErrors) On Error GoTo 0 If TestRng Is Nothing Then 'keep looking Else MsgBox "Errors in Formulas in this range" Exit Sub End If Application.ScreenUpdating = False Set TempWks = Worksheets.Add For Each myArea In mySel.Areas myArea.Copy TempWks.Range(myArea.Cells(1).Address).PasteSpecia l Paste:=xlPasteValues Next myArea TempWks.Cells.Replace what:="*" & myWord & "*", replacement:="#N/A", _ lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False Set TestRng = Nothing On Error Resume Next Set TestRng = TempWks.Cells.SpecialCells(xlCellTypeConstants, xlErrors) On Error GoTo 0 If TestRng Is Nothing Then MsgBox "No cells with: " & myWord & " in it" Else Set TestRng = CurWks.Range(TestRng.Address) End If Application.DisplayAlerts = False TempWks.Delete Application.DisplayAlerts = True If TestRng Is Nothing Then 'do nothing Else Application.Goto TestRng End If Application.ScreenUpdating = True End Sub h2fcell wrote: Hello, Iâm looking for a way to select all cells in a current region with a certain value. Similar to what Go To Special does with blanks or constants. Selection.SpecialCells(xlCellTypeConstants, 23).Select Iâd like to avoid using loops or If then, because the region is quite large and there are few cells that meet the condition. The value is the word âœtestâ. Is there a way to use Selection.SpecialCells for cells containing the word test? After all the cells containing the word test are selected, Iâll change the selections format. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select | Excel Programming | |||
Excel 2003 'SpecialCells' VBA cell Selection - how do I do this? Helpneeded please | Excel Programming | |||
SpecialCells - no cells selected | Excel Programming | |||
SpecialCells - no cells selected | Excel Programming | |||
.Cells.SpecialCells(xlLastCell) | Excel Programming |