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