مشكلة فى تكرار نتيجة حرف البحث

Option Explicit



Private Sub TextBox1_Change()

Dim sh As Worksheet

Set sh = Sheets("data2")

Dim i

Dim x, y, m

Dim LR

Dim p

m = 0

Me.ListBox1.Clear

For y = 1 To 10

 ListBox1.AddItem

 ListBox1.List(0, m) = sh.Cells(2, y)

m = m + 1

 Next y



ListBox1.ColumnCount = 10

i = 1

LR = sh.Range("a" & Rows.Count).End(xlUp).Row

For x = 3 To LR

If sh.Cells(x, "d") Like "*" & TextBox1 & "*" Then

ListBox1.AddItem

ListBox1.List(i, 0) = sh.Cells(x, "a")

ListBox1.List(i, 1) = sh.Cells(x, "b")

ListBox1.List(i, 2) = sh.Cells(x, "c")

ListBox1.List(i, 3) = sh.Cells(x, "d")

ListBox1.List(i, 4) = sh.Cells(x, "e")

ListBox1.List(i, 5) = sh.Cells(x, "f")

ListBox1.List(i, 6) = sh.Cells(x, "g")

ListBox1.List(i, 7) = sh.Cells(x, "h")

ListBox1.List(i, 8) = sh.Cells(x, "i")

ListBox1.List(i, 9) = sh.Cells(x, "j")



i = i + 1

End If

Next x



End Sub

Private Sub TextBox1_Change()

Dim sh As Worksheet

Dim i As Long

Dim SearchColumn As Long

Dim Lrow As Long



Set sh = Sheets("data2")

Me.ListBox1.Clear

'For Listbox Header

Me.ListBox1.AddItem "التاريخ"

Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "رقم القيد"

Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "الحساب"

Me.ListBox1.List(ListBox1.ListCount - 1, 3) = "صاحب الحساب"

Me.ListBox1.List(ListBox1.ListCount - 1, 4) = "عدد"

Me.ListBox1.List(ListBox1.ListCount - 1, 5) = "مبلغ"

Me.ListBox1.List(ListBox1.ListCount - 1, 6) = "اجمالى"

Me.ListBox1.List(ListBox1.ListCount - 1, 7) = "سداد/تحصيل"

Me.ListBox1.List(ListBox1.ListCount - 1, 8) = "شرح القيد"

Me.ListBox1.List(ListBox1.ListCount - 1, 9) = "ترصيد"

'------------------------------------------------------



If OptionButton1 Then

    SearchColumn = 2

ElseIf OptionButton2 Then

    SearchColumn = 3

Else

    SearchColumn = 4

End If

Lrow = sh.Cells(sh.Rows.Count, SearchColumn).End(xlUp).Row

Me.ListBox1.Selected(0) = True

For i = 3 To Lrow

    If sh.Cells(i, SearchColumn).Value Like "*" & Me.TextBox1.Value & "*" And Me.TextBox1.Value <> "" Then

        With Me.ListBox1

            .AddItem sh.Cells(i, 2)

            .List(.ListCount - 1, 0) = sh.Cells(i, 1)

            .List(.ListCount - 1, 1) = sh.Cells(i, 2)

            .List(.ListCount - 1, 2) = sh.Cells(i, 3)

            .List(.ListCount - 1, 3) = sh.Cells(i, 4)

            .List(.ListCount - 1, 4) = sh.Cells(i, 5)

            .List(.ListCount - 1, 5) = sh.Cells(i, 6)

            .List(.ListCount - 1, 6) = sh.Cells(i, 7)

            .List(.ListCount - 1, 7) = sh.Cells(i, 8)

            .List(.ListCount - 1, 8) = sh.Cells(i, 9)

            .List(.ListCount - 1, 9) = sh.Cells(i, 10)

            'هذا السطر خاص بالترصيد

            Dim a&, b&, c&

            a = IIf(.List(.ListCount - 1, 6) <> "", .List(.ListCount - 1, 6), 0)

            b = IIf(.List(.ListCount - 1, 7) <> "", .List(.ListCount - 1, 7), 0)

    '        c = IIf(.List(.ListCount - 1, 9) <> "", .List(.ListCount - 1, 9), 0)

            If .ListCount - 1 > 0 Then

                .List(.ListCount - 1, 9) = a - b

    '        Else

    '            .List(.ListCount - 1, 9) = a - b + c

            End If

        End With

    End If

Next i

End Sub

Dim ii As Integer

    If OptionButton3.Object Then

        Me.ListBox1.Selected(0) = True

        For i = 3 To sh.Range("C" & Rows.Count).End(xlUp).Row

            If InStr(sh.Cells(i, 4), LCase(TextBox1.Text)) And TextBox1.Text <> "" Then

                With Me.ListBox1

                    .AddItem sh.Cells(i, 2)

                    For ii = 0 To 9

                        .List(ListBox1.ListCount - 1, ii) = sh.Cells(i, ii + 1)

                    Next ii

                    If .ListCount - 1 = 1 Then

                        .List(ListBox1.ListCount - 1, 9) = Val(.List(ListBox1.ListCount - 1, 6)) - Val(.List(ListBox1.ListCount - 1, 7))

                    Else

                        .List(ListBox1.ListCount - 1, 9) = Val(.List(ListBox1.ListCount - 1, 6)) - Val(.List(ListBox1.ListCount - 1, 7)) + Val(.List(ListBox1.ListCount - 2, 9))

                    End If

                End With

            End If

        Next i

    End If