I am using Excel 97, I have a column of data which needs separating I am
wanting to find each cell that contains in part of the cell the word
'investigating' then with each cell found move it to the next column to the
right.

I have tried the instr function but obviously doing something wrong!

Any help would be appreciated.

--
Mark

For Each cell in Selection
If LCase(cell.Value) Like "*investigating*"
cell.Offset(0,1).Value = cell.Value
End If
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mark" wrote in message
...
I am using Excel 97, I have a column of data which needs separating I am
wanting to find each cell that contains in part of the cell the word
'investigating' then with each cell found move it to the next column to
the
right.

I have tried the instr function but obviously doing something wrong!

Any help would be appreciated.

--
Mark

Hi Mark,

Try something like:

'=================
Public Sub Tester001()
Dim rng As Range
Dim rCell As Range
Dim WB As Workbook
Dim SH As Worksheet
Const sStr As String = "investigating" '<<===== CHANGE

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set rng = SH.Range("A1:A100") ' <<===== CHANGE

For Each rCell In rng.Cells
With rCell
If InStr(1, .Value, sStr, vbTextCompare) Then
rCell.Copy rCell(1, 2)
rCell.ClearContents
End If
End With
Next rCell
End Sub
'<<=================


---
Regards,
Norman



"Mark" wrote in message
...
I am using Excel 97, I have a column of data which needs separating I am
wanting to find each cell that contains in part of the cell the word
'investigating' then with each cell found move it to the next column to
the
right.

I have tried the instr function but obviously doing something wrong!

Any help would be appreciated.

--
Mark

Dim rng as Range, cell as Range
With ActiveSheet
set rng = intersect(.columns(3),.UsedRange)
End with
set cell = .Find(what:="investigating",Lookat:=xlPart)
if not cell is nothing then
do
cell.offset(0,1).Value = cell.value
cell.clearcontents
set cell = rng.findnext(cell)
Loop while not cell is nothing
end if

--
Regards,
Tom Ogilvy


"Mark" wrote in message
...
I am using Excel 97, I have a column of data which needs separating I am
wanting to find each cell that contains in part of the cell the word
'investigating' then with each cell found move it to the next column to
the
right.

I have tried the instr function but obviously doing something wrong!

Any help would be appreciated.

--
Mark

Mark, Apart from the other replies turn on the macro recorder and do a
Find from the Excel menu then look at the code, might help.

Mark wrote:
I am using Excel 97, I have a column of data which needs separating I am
wanting to find each cell that contains in part of the cell the word
'investigating' then with each cell found move it to the next column to the
right.

I have tried the instr function but obviously doing something wrong!

Any help would be appreciated.