PDA

View Full Version : Solved: Removing text from a field



ukdane
01-06-2009, 04:11 AM
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
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

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

For example: 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

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

Cheers

Bob Phillips
01-06-2009, 04:24 AM
Not sure that this is totally comprehensive, but ...



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

ukdane
01-07-2009, 01:26 AM
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
01-07-2009, 01:47 AM
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

ukdane
01-07-2009, 01:59 AM
With a small adjustment:
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

It works a treat.

Thanks a million.