Informe por código de producto

sub busca_codigo ()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Sheets("informe").Range("b4:i6").ClearContents
Sheets("informe").Range("a10:e10000").ClearContents
Dim i As Integer
Dim L As Integer
Dim j As Integer
Dim VALOR As String
Dim CONTAR As Double
For i = 1 To 1000
'ENTRADAS
If Hoja4.Cells(i, 1) = "" Then
final = i - 1
Exit For
End If
Next
VALOR = Range("b3").Value
' salida
CONTAR = 10
For L = 1 To 1000
If Hoja4.Cells(L, 1) = VALOR Then
Sheets("informe").Cells(4, 2) = Hoja4.Cells(L, 2)
Sheets("informe").Cells(5, 2) = Hoja4.Cells(L, 3)
Exit For
End If
Next
For j = 1 To final
If Hoja4.Cells(j, 1) = VALOR Then
'***** funciona salida
Sheets("informe").Cells(CONTAR, 1) = Hoja4.Cells(j, 6)
Sheets("informe").Cells(CONTAR, 2) = Hoja4.Cells(j, 4)
Sheets("informe").Cells(CONTAR, 3) = Hoja4.Cells(j, 5)
Sheets("informe").Cells(CONTAR, 4) = 1 * Hoja4.Cells(j, 3)
CONTAR = CONTAR + 1
End If
Next
End Sub