VBA, Check for entry and overwrite if it does

[grabrail]

Hi

I have some code that monitors a cell with a drop down box, there are 3 options in the dropdown box and if 2 out of the 3 options are selected the code will create an entry elsewhere on the worksheet in a summary area.

This works perfectly. However:

There is only one entry allowed for this particular data, so if the user goes back and changes their mind on which option to use, it will create a second entry in the summary area.

What I would like to do is if an entry is made, and then changed, the second entry will overwrite or remove the first entry and only create the new entry. The code I have that works is as follows:

    If Target.Value = "Excessive" Or Target.Value = "Inadequate" Then
        
                             
                Worksheets("VI Sheet").Range("A112").Select
                
                'get next blank cell
                While ActiveCell.Value <> ""
                    ActiveCell.Offset(1, 0).Range("A1").Select
                Wend
                
                'input results
                
                'box 1
                ActiveCell.FormulaR1C1 = "47"
                ActiveCell.Offset(0, 1).Range("A1").Select
                
                'box 2
                ActiveCell.FormulaR1C1 = "0"
                ActiveCell.Offset(0, 1).Range("A1").Select
                
                'box 3
                ActiveCell.FormulaR1C1 =  Target.Value & " requires attention "
                ActiveCell.Offset(0, 5).Range("A1").Select
                
                'box 4
                ActiveCell.FormulaR1C1 = "S"
                ActiveCell.Offset(0, 3).Range("A1").Select
                
                    
                'box 5
                ActiveCell.FormulaR1C1 = "FW"
                ActiveCell.Offset(0, -2).Range("A1").Select
                
                 
                ActiveCell.FormulaR1C1 = "0"
                
            End If

How can I put a check in to confirm if an entry already exists, then delete/overwrite with new entry?

 

[grabrail]

The entry could be on any one of these lines by the way

 

[grabrail]

I managed to get this working by wrapping the original code in the following code

Dim rngFound as Range
With Worksheets("VI Sheet").range("C113:C141")
    Set rngFound  = .Find("requires attention", LookIn:=xlValues)
    If Not rngFound Is Nothing Then 

                            ActiveCell.FormulaR1C1 = ""
                            ActiveCell.Offset(0, -1).Range("A1").Select
                            
                            ActiveCell.FormulaR1C1 = ""
                            ActiveCell.Offset(0, -1).Range("A1").Select
                            
                            ActiveCell.FormulaR1C1 = ""
                            ActiveCell.Offset(0, 7).Range("A1").Select
                            
                            ActiveCell.FormulaR1C1 = ""
                            ActiveCell.Offset(0, 1).Range("A1").Select
                            
                            ActiveCell.FormulaR1C1 = ""
            
                           <Then above code here>
    application.got rngFound, true


    else
         <Then above code here>
    End If
End With