Sizimi kıracağız Halil hocam yapmışken Find ve Findnext ilede yapalım.
Tabii sizin kod daha kullanışlı gibi.

Private Sub ComboBox1_Change()

    Dim bul As Range, adres As String, aranan As String



    Me.ComboBox3.Clear: Me.ComboBox4.Clear

    With Sayfa3

        Set bul = .Range("H:H").Find(Me.ComboBox1.Value, , xlValues, 1)

        If Not bul Is Nothing Then aranan = bul.Offset(0, -1).Value

        getir "D", Me.ComboBox3, .Name, aranan

        getir "A:A", Me.ComboBox4, .Name, aranan

            

    End With

    Set bul = Nothing

End Sub

Sub getir(alan As String, cbo As MSForms.ComboBox, sayfa As String, aranan As String)

    If aranan = "" Then Exit Sub

    With ThisWorkbook.Sheets(sayfa)

        Set bul = .Range(alan).Find(aranan, , xlValues, 1)

        If Not bul Is Nothing Then

            adres = bul.Address

            Do

                cbo.AddItem bul.Offset(0, 1).Value

                Set bul = .Range(alan).FindNext(bul)

            Loop While Not bul Is Nothing And adres <> bul.Address

        End If

    End With

End Sub

Private Sub UserForm_Initialize()

    sonstr = Sayfa3.Cells(Sayfa3.Rows.Count, "h").End(xlUp).Row

    Me.ComboBox1.List = Sayfa3.Range("h2:h" & sonstr).Value

End Sub