Use Selection.SpecialCells for cells with a particular value?

[h2fcell]

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.

 

[Don Guillett]

Post some sample data with several that do contain "test"

 

[h2fcell]

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

 

[Dave Peterson]

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).PasteSpecial 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]

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 don’t normally have errors in the region.

Below is the code I’m 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.

 

[ShaneDevenshire]

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.

 

[Dave Peterson]

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.