Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
search for absense of data
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 -- Hammer_75 ----------------------------------------------------------------------- Hammer_757's Profile: http://www.excelforum.com/member.php...nfo&userid=741 View this thread: http://www.excelforum.com/showthread.php?threadid=31399 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search data? | Excel Discussion (Misc queries) | |||
Data Search | Excel Discussion (Misc queries) | |||
Data Search | Excel Worksheet Functions | |||
Search through Data | New Users to Excel | |||
Search for data using 'contains' | Excel Discussion (Misc queries) |