IF ACTIVECELL contains

[FIRSTROUNDKO via OfficeKB.com]

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

[papou]

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

[Tom Ogilvy]

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
>

[Gary''s Student]

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
>

[Tom Ogilvy]

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
> >