DELETE PAST FORMULA IN EMPTY CELLS

hi ,

i have a userform to pull data in between multiple excel files. i need to set a target for each group.

in this case , cat, dog, otter,rabbit

but however when i search more than once in the userform, the previous formula on the blank cell appears. how do i go about??

thanks

Code:

---

Sub Macro1()

Dim Row As Integer
Dim Blank As Integer

Row = 2
Blank = 0

With Sheet4

    Do While Blank <= 5
   
    If .Cells(Row, 5).Value = "CAT" Then
    .Cells(Row, 6).Value = "9"
    Row = Row + 1
   
    ElseIf .Cells(Row, 5).Value = "DOG" Then
    .Cells(Row, 6).Value = "35"
    Row = Row + 1
   
    ElseIf .Cells(Row, 5).Value = "RABBIT" Then
    .Cells(Row, 6).Value = "50"
    Row = Row + 1
 
  ElseIf .Cells(Row, 5).Value = "OTTER" Then
    .Cells(Row, 6).Value = "6"
  Row = Row + 1
 
  Else
 
    .Cells(Row, 6).Value = "2"
  Row = Row + 1
         
         
        End If
       
        '--- For detect end of row
        If .Cells(Row, 5).Value = "" Then
            Blank = 6
           
        Else
            '--- Reset
            Blank = 0
        End If
    Loop
    End With

End Sub

---

attach please a sample file and explain better your goal