search for absense of data

[Hammer_757]

I am working with a spreadsheet that is being used primarily as
database with several elements of alphanumeric data in each cell.
need to search for cells that are missing an element “(3-2)” and the
search for something else “P POS” within the cell, replace it wit
something, and continue checking the remaining cells. If a cell ha
“(3-2)” in it I need to bypass the cell.

I cannot just search for “P POS” because it needs to remain in th
cells that contain (3-2).

For starters, I cannot seem to figure out how to find the absence o
something…

Thanks in advance
Rober

 

[Dave Peterson]

Maybe just looking through the cells would be enough:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim Str3_2 As String
Dim StrP_POS As String
Dim P_POS_StartsAt As Long
Dim NewString As String

Str3_2 = "(3-2)"
StrP_POS = "P POS"
NewString = "hi there"

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlTextValues)

'whole sheet???
'Set myRng = .UsedRange.Cells.SpecialCells(xlTextValues)

On Error GoTo 0
For Each myCell In myRng.Cells
If InStr(1, myCell.Value, Str3_2, vbTextCompare) > 0 Then
'it's in there
'do nothing
Else
P_POS_StartsAt = InStr(1, myCell.Value, StrP_POS, vbTextCompare)
If P_POS_StartsAt > 0 Then
myCell.Value = Left(myCell.Value, P_POS_StartsAt - 1) _
& NewString _
& Mid(myCell.Value, P_POS_StartsAt _
+ Len(StrP_POS))

Else
'do nothing
End If
End If
Next myCell
End With

End Sub