Solved: Removing text from a field

[ukdane]

My worksheet contains 3 fields where the user can enter "Y" for yes or "N" for no. There is a 4th field, which is for the user to input text.

I have written code so that if any of the 3 fields contain a "Y" then a text is added to the 4th text field.

My problem is that is the user enters "N" in any of the 3 fields, the code needs to tjek for the text in the 4th field, and remove it if it is found.

Here's the code I have so far
[VBA]Sub alerttjek()
Dim myRow As String
Dim myStatusplace As String
Dim mystatus As String
myRow = ActiveCell.Row

'Input text in cell BL for IMO, Insurance, Remburs
If ActiveSheet.Range("AY" & myRow) = "Y" Then
ActiveSheet.Range("BL" & myRow) = ActiveSheet.Range("BL" & myRow) & " REMBURS"
End If
If ActiveSheet.Range("AZ" & myRow) = "Y" Then
ActiveSheet.Range("BL" & myRow) = ActiveSheet.Range("BL" & myRow) & " IMO"
End If
If ActiveSheet.Range("BA" & myRow) = "Y" Then
ActiveSheet.Range("BL" & myRow) = ActiveSheet.Range("BL" & myRow) & " FORSIKRING"
End If

End Sub[/VBA]

So for each line I need to add a "N" to the if statement that will remove the relevant text.

For example: [VBA]If ActiveSheet.Range("BA" & myRow) = "Y" Then
ActiveSheet.Range("BL" & myRow) = ActiveSheet.Range("BL" & myRow) & " FORSIKRING"
Else if
ActiveSheet.Range("BA" & myRow) = "N" Then 'look for " FORSIKRING" in field "BL" & myRow and remove it from the field

End If[/VBA]

How to I search for the text, and how do I then remove it?

Cheers

[Bob Phillips]

Not sure that this is totally comprehensive, but ...

[vba]

Sub alerttjek()
Dim myRow As String
Dim myStatusplace As String
Dim mystatus As String
myRow = ActiveCell.Row

With ActiveSheet

'Input text in cell BL for IMO, Insurance, Remburs
If .Range("AY" & myRow) = "Y" Then
.Range("BL" & myRow) = .Range("BL" & myRow) & " REMBURS"
Else
.Range("BL" & myRow) = Replace(.Range("BL" & myRow), " REMBURS", "")
End If
If .Range("AZ" & myRow) = "Y" Then
.Range("BL" & myRow) = .Range("BL" & myRow) & " IMO"
Else
.Range("BL" & myRow) = Replace(.Range("BL" & myRow), " IMO", "")
End If
If .Range("BA" & myRow) = "Y" Then
.Range("BL" & myRow) = .Range("BL" & myRow) & " FORSIKRING"
Else
.Range("BL" & myRow) = Replace(.Range("BL" & myRow), " FORSIKRING", "")
End If
End With
End Sub
[/vba]

[ukdane]

XLD: Thanks, in theory this works. BUT everytime the user changes the cell to Y, it adds the text again.
I only need it to be added once.

I need to add a routine that checks for the existence of the text, and only adds it if it doesn't exist.

[Bob Phillips]

[vba]

Sub alerttjek()
Dim myRow As String
Dim myStatusplace As String
Dim mystatus As String
myRow = ActiveCell.Row

With ActiveSheet

'Input text in cell BL for IMO, Insurance, Remburs
.Range("BL" & myRow) = Replace(.Range("BL" & myRow), " REMBURS", "")
If .Range("AY" & myRow) = "Y" Then
.Range("BL" & myRow) = .Range("BL" & myRow) & " REMBURS"
End If
.Range("BL" & myRow) = Replace(.Range("BL" & myRow), " IMO", "")
If .Range("AZ" & myRow) = "Y" Then
.Range("BL" & myRow) = .Range("BL" & myRow) & " IMO"
End If
.Range("BL" & myRow) = Replace(.Range("BL" & myRow), " FORSIKRING", "")
If .Range("BA" & myRow) = "Y" Then
.Range("BL" & myRow) = .Range("BL" & myRow) & " FORSIKRING"
End If
End With
End Sub
[/vba]

[ukdane]

With a small adjustment:
[VBA]Sub alerttjek()
Dim myRow As String
Dim myStatusplace As String
Dim mystatus As String
myRow = ActiveCell.Row

With ActiveSheet

'Input text in cell BL for IMO, Insurance, Remburs
.Range("BL" & myRow) = Replace(.Range("BL" & myRow), "REMBURS", "")
If .Range("AY" & myRow) = "Y" Then
.Range("BL" & myRow) = .Range("BL" & myRow) & " REMBURS"
End If
.Range("BL" & myRow) = Replace(.Range("BL" & myRow), "IMO", "")
If .Range("AZ" & myRow) = "Y" Then
.Range("BL" & myRow) = .Range("BL" & myRow) & " IMO"
End If
.Range("BL" & myRow) = Replace(.Range("BL" & myRow), "FORSIKRING", "")
If .Range("BA" & myRow) = "Y" Then
.Range("BL" & myRow) = .Range("BL" & myRow) & " FORSIKRING"
End If
End With
End Sub [/VBA]

It works a treat.

Thanks a million.