DELETE PAST FORMULA IN EMPTY CELLS

[Zayn_21]

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

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