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

[patel]

attach please a sample file and explain better your goal