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

 

[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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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