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