Hi!

at the moment I am trying to write a code that looks at a list of cells and

1) evaluates an activecell in col a as being greater then zero
and if so
2) if Cell b on the same row contains the text "apple" i.e English Apple
then delete apple from cell

Before

-1 English apple
0 Welsh apple
1 Scottish apple
2 French apple

After

-1 English apple
0 Welsh
1 Scottish
2 French

Thanks in advance

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

Hello
Please amend accordingly:
For Each c In Range("A1:A5")
If c.Value 0 Then
If c.Offset(0, 1).Value Like "*apple" Then
c.Offset(0, 1).Value = Left(c.Offset(0, 1).Value, Len(c.Offset(0,
1).Value) - 6)
End If
End If
Next c


HTH
Cordially
Pascal

"FIRSTROUNDKO via OfficeKB.com" <u15639@uwe a écrit dans le message de
news: 5fb3ec530bc84@uwe...
Hi!

at the moment I am trying to write a code that looks at a list of cells
and

1) evaluates an activecell in col a as being greater then zero
and if so
2) if Cell b on the same row contains the text "apple" i.e English Apple
then delete apple from cell

Before

-1 English apple
0 Welsh apple
1 Scottish apple
2 French apple

After

-1 English apple
0 Welsh
1 Scottish
2 French

Thanks in advance

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

Sub FixData()
Dim lastrow as long, i as long
Dim ipos as Long, s as String

lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 1 step -1
if isnumeric(cells(i,1)) then
if cells(i,1) = 0 then
s = cells(i,2).Value
ipos = Instr(1,s,"apple",vbTextCompare)
if ipos < 0 then
s = Left(s,ipos-1) & Mid(s,ipos+5,len(s))
cells(i,2).Value = Application.Trim(s)
end if
end if
end if
Next i
End Sub

--
Regards,
Tom Ogilvy


"FIRSTROUNDKO via OfficeKB.com" wrote:

Hi!

at the moment I am trying to write a code that looks at a list of cells and

1) evaluates an activecell in col a as being greater then zero
and if so
2) if Cell b on the same row contains the text "apple" i.e English Apple
then delete apple from cell

Before

-1 English apple
0 Welsh apple
1 Scottish apple
2 French apple

After

-1 English apple
0 Welsh
1 Scottish
2 French

Thanks in advance

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

Assuming that you SELECT a cell on the worksheet first:

Sub fruitkiller()
Dim r As Range
Set r = Selection
If r.Value 0 Then
r.Offset(0, 1).Value = Application.Substitute(r.Offset(0, 1).Value, "apple",
"")
End If
End Sub

This only does the ActiveCell. You can put it in a loop to move down the
entire column.
--
Gary''s Student


"FIRSTROUNDKO via OfficeKB.com" wrote:

Hi!

at the moment I am trying to write a code that looks at a list of cells and

1) evaluates an activecell in col a as being greater then zero
and if so
2) if Cell b on the same row contains the text "apple" i.e English Apple
then delete apple from cell

Before

-1 English apple
0 Welsh apple
1 Scottish apple
2 French apple

After

-1 English apple
0 Welsh
1 Scottish
2 French

Thanks in advance

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

While it may not be a problem here,
It is also worthy to note that substitute is case sensitive as shown from
the immediate window:

? application.substitute("French Apple","apple","")
French Apple

--
Regards,
Tom Ogilvy


"Gary''s Student" wrote:

Assuming that you SELECT a cell on the worksheet first:

Sub fruitkiller()
Dim r As Range
Set r = Selection
If r.Value 0 Then
r.Offset(0, 1).Value = Application.Substitute(r.Offset(0, 1).Value, "apple",
"")
End If
End Sub

This only does the ActiveCell. You can put it in a loop to move down the
entire column.
--
Gary''s Student


"FIRSTROUNDKO via OfficeKB.com" wrote:

Hi!

at the moment I am trying to write a code that looks at a list of cells and

1) evaluates an activecell in col a as being greater then zero
and if so
2) if Cell b on the same row contains the text "apple" i.e English Apple
then delete apple from cell

Before

-1 English apple
0 Welsh apple
1 Scottish apple
2 French apple

After

-1 English apple
0 Welsh
1 Scottish
2 French

Thanks in advance

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1