Sub Кнопка1_Щелчок()
Dim iLastRow As Long, iStartRow As Long, iEndRow As Long
Dim iNumber As Long
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 4 To iLastRow
If iStartRow = 0 Then iStartRow = i
iNumber = Cells(i, "B").Value
If Cells(i + 1, "B")<> iNumber Then iEndRow = i
'основной код
End Sub

Private Sub UserForm_Initialize()
ComboBox1.List = Worksheets("Лист1").Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp)).Value
End Sub
Private Sub CommandButton1_Click()
With Worksheets("Лист1")
Set a = .Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp)).Value
a.Find(What:=ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Value
End With
ActiveCell.Value = a
End Sub

Sub Кнопка1_Щелчок()n = Worksheets("Лист2" ).Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To n     
With Worksheets("Лист1")    
LastRow = .Cells(65536, 1).End(xlUp).Row + 1     
.Cells(LastRow, 8).Value = Worksheets("Лист2") .Cells(i, 2).Value     
End With
Next i
End Sub

Private Sub UserForm_Initialize()
ComboBox1.List = Worksheets("Лист4").Range(Range("B4"), Range("B" & Rows.Count).End(xlUp)).Value
End Sub 
 

Private Sub CommandButton1_Click() 
    Application.ScreenUpdating = False                 
    Application.Calculation = xlCalculationManual

     With Worksheets("Лист1")
     LastRow = .Cells(65536, 1).End(xlUp).Row + 1
     .Cells(LastRow, 1).Value = TextBox1.Text           
     .Cells(LastRow, 2).Value = TextBox2.Text          
     .Cells(LastRow, 3).Value = TextBox3.Text
     .Cells(LastRow, 4).Value = TextBox4.Text
     .Cells(LastRow, 6).Value = TextBox5.Text
     .Cells(LastRow, 8).Value = ComboBox1.Value
     End With
    
    Application.Calculation = xlCalculationAutomatic    
    Application.ScreenUpdating = True                
End Sub
 

Private Sub UserForm_Initialize()
Dim iMassiv As Range
Set iMassiv = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
ComboBox1.Value = iMassiv
End Sub