ExcelBanter - search for absense of data

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

Hammer_757 wrote:

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

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

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

Thanks in advance
Robert

--
Hammer_757
------------------------------------------------------------------------
Hammer_757's Profile: http://www.excelforum.com/member.php...fo&userid=7413
View this thread: http://www.excelforum.com/showthread...hreadid=313991

--

Dave Peterson