اذهب الي المحتوي
أوفيسنا
بحث مخصص من جوجل فى أوفيسنا
Custom Search

مساعدة في عمل اقل كمية

Ashraf Ehab

بواسطه Ashraf Ehab
في منتدى الاكسيل Excel

 مشاركة
إذهب إلى أفضل إجابة Solved by أ / محمد صالح,

الردود الموصى بها

Ashraf Ehab

Ashraf Ehab

قام بنشر
قام بنشر

السلام عليكم ورحمة الله وبركاته

الاساتذة الافاضل انا احتاج مساعدة في عمل هذا الكود

انا اريد وقت اضافة صنف يوجد تكست بوكس اسمه اقل كمية 

يتم الكتابة فيه اقل كمية وحين وصول الكمية اقل من هذا العدد او يساوية يقوم بنقل بيانات الصنف الي الشيت الاخر 

items.xlsm

رابط هذا التعليق
شارك
  • أفضل إجابة
أ / محمد صالح

أ / محمد صالح

قام بنشر
  • أفضل إجابة
قام بنشر

علبكم السلام ورحمة الله زبركاته

أعتقد أن الطريقة الصحيحة لبرمجة النواقص هي:

في شاشة إضافة صنف يتم إضافة أقل قيمة أو كمية التنبيه أو أي تسمية وتسجل في الشيت الخاص بالأصناف

وفي شاشة عمليات البيع يتم التأكد من أن العدد المطلوب أكبر من العدد المتبقي في المخزن وحساب العدد المتبقي بعد عملية البيع

فإذا كان أقل من كمية التنبيه يتم إضافته للنواقص

مع ضرورة تفريغ جدول النواقص بمجرد شراء هذه النواقص

بالتوفيق

  • Like 1
رابط هذا التعليق
شارك
Ashraf Ehab

Ashraf Ehab

قام بنشر
  • الكاتب
قام بنشر

نصيحة ممتازة واشكرك علي هذه النصيحة ولكن ظهر لي خطاء في حالة بيع كمية اخري وهي في النواقص تخصم الكمية فقط ولا تضيف البيانات مرة اخري وحللت هذه المكشلة ولكن اذا بعت صنف جديد ليس في القائمة يعطيني هذا الخطا 

9.jpg.b06692b9d376c0691e871f3799faaad0.jpg

وهذا هو الكود  

Dim c As Range
    Dim firstAddress, nfe As String
    Dim f As Integer
    Dim x As Integer
    Dim Y As Integer
    Dim last, last1, lse As Integer
last = SelesSheet.Range("A10000").End(xlUp).Row + 1
last1 = ShortfallsSheet.Range("A10000").End(xlUp).Row + 1
For i = 0 To ListBox1.ListCount - 1
ShortfallsSheet.Range("O1").Value = Me.ListBox1.List(i, 4)
lse = ShortfallsSheet.Range("N1").Value

       f = Me.ListBox1.List(i, 3)
       x = Me.ListBox1.List(i, 1)
       With ItemsSheet.Range("B:B")
        Set c = .Find(f, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
        Y = ItemsSheet.Cells(c.Row, "D").Value
            Y = Y - x
        a = ItemsSheet.Cells(c.Row, "A").Value
            a = a
        b = ItemsSheet.Cells(c.Row, "B").Value
            b = b
        cc = ItemsSheet.Cells(c.Row, "C").Value
            cc = cc
        d = ItemsSheet.Cells(c.Row, "D").Value
            d = d
        e = ItemsSheet.Cells(c.Row, "E").Value
            e = e
        ff = ItemsSheet.Cells(c.Row, "F").Value
            ff = ff
        g = ItemsSheet.Cells(c.Row, "G").Value
            gg = gg
        j = ItemsSheet.Cells(c.Row, "J").Value
            j = j
SelesSheet.Cells(last, "I").Value = Me.ListBox1.List(i, 4)
SelesSheet.Cells(last, "J").Value = Me.ListBox1.List(i, 3)
SelesSheet.Cells(last, "K").Value = Me.ListBox1.List(i, 2)
SelesSheet.Cells(last, "L").Value = Me.ListBox1.List(i, 1)
SelesSheet.Cells(last, "M").Value = Me.ListBox1.List(i, 0)
ItemsSheet.Cells(c.Row, "D").Value = Y
last = last + 1
End If
End With
Next i
nfe = ShortfallsSheet.Cells(lse, "A").Value
If a = nfe Then
ShortfallsSheet.Cells(lse, "D").Value = d - x
ElseIf d <= j Then
ShortfallsSheet.Cells(last1, "A").Value = a
ShortfallsSheet.Cells(last1, "B").Value = b
ShortfallsSheet.Cells(last1, "C").Value = cc
ShortfallsSheet.Cells(last1, "D").Value = d - x
ShortfallsSheet.Cells(last1, "E").Value = e
ShortfallsSheet.Cells(last1, "F").Value = ff
ShortfallsSheet.Cells(last1, "G").Value = g
last1 = last1 + 1
lse = lse + 1
End If
    Dim ls1 As Integer
    Dim ls2 As Integer
ls1 = SelesSheet.Range("A10000").End(xlUp).Row + 1
ls2 = SelesSheet.Range("I10000").End(xlUp).Row
For s = ls1 To ls2
SelesSheet.Cells(ls1, "A").Value = Me.TextBox1.Value
SelesSheet.Cells(ls1, "B").Value = Me.TextBox2.Value
SelesSheet.Cells(ls1, "C").Value = Me.TextBox3.Value
SelesSheet.Cells(ls1, "D").Value = Me.ComboBox1.Value
SelesSheet.Cells(ls1, "E").Value = Me.TextBox13.Value
SelesSheet.Cells(ls1, "F").Value = Me.TextBox11.Value
SelesSheet.Cells(ls1, "G").Value = Me.TextBox12.Value
SelesSheet.Cells(ls1, "H").Value = Me.TextBox14.Value
ls1 = ls1 + 1

Next s
MsgBox "Êã ÇÖÇÝÉ ÇáÈíÇäÇÊ ÈäÌÇÍ", vbInformation + vbMsgBoxRight + vbMsgBoxRtlReading, "ÊÇßíÏ"

والله الموفق للجميع

 

dwork.xlsm

رابط هذا التعليق
شارك
Ashraf Ehab

Ashraf Ehab

قام بنشر
  • الكاتب
قام بنشر

بالاضافة الي في المشتريات بحاول اما يجد العمود يقوم بمسحه ولكن الكود لا ينفذ وهذا هو الكود  

Dim qp As Integer
Dim rp As Integer
Dim wp As Integer
Dim ep As Integer
qp = purchasesSheet.Cells(Rows.Count, "D").End(xlUp).Row + 1
wp = ItemsSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
ep = suppliersSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
rp = Me.ListBox1.ListCount
    Dim t As Range
    Dim firsttAddress, ldelet As String
    Dim u As Integer
    Dim i As Integer
    Dim O As Integer
            Dim ii As Long
For ii = 1 To Me.ListBox1.ListCount - 1
ShortfallsSheet.Range("O1").Value = Me.ListBox1.List(ii, 3)
ldelet = ShortfallsSheet.Range("N1").Value


    u = Me.ListBox1.List(ii, 4)
    i = Me.ListBox1.List(ii, 6)


    With ItemsSheet.Range("B:B")
        Set t = .Find(u, LookIn:=xlValues)
        If Not t Is Nothing Then
            firsttAddress = t.Address
        O = ItemsSheet.Cells(t.Row, "D").Value
            O = O + i
        d = ItemsSheet.Cells(t.Row, "D").Value
            d = d
        j = ItemsSheet.Cells(t.Row, "J").Value
            j = j

purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Me.ListBox1.List(ii, 0)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Me.ListBox1.List(ii, 1)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Me.ListBox1.List(ii, 2)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Me.ListBox1.List(ii, 3)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Me.ListBox1.List(ii, 4)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = Me.ListBox1.List(ii, 5)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 6) = Me.ListBox1.List(ii, 6)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 7) = Me.ListBox1.List(ii, 7)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 8) = Me.ListBox1.List(ii, 8)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 9) = Me.ListBox1.List(ii, 9)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 10) = Me.ListBox1.List(ii, 10)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 11) = Me.ListBox1.List(ii, 11)
'purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 12) = Me.ListBox1.List(ii, 12)
'purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 13) = Me.ListBox1.List(ii, 13)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 12) = Me.TextBox13.Value
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 13) = Me.TextBox14.Value
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 14) = Time
ItemsSheet.Cells(t.Row, "D").Value = O 'Me.TextBox7.Value
ItemsSheet.Cells(t.Row, "G").Value = Me.ListBox1.List(ii, 9)
ItemsSheet.Cells(t.Row, "H").Value = Me.ListBox1.List(ii, 10)
ItemsSheet.Cells(t.Row, "I").Value = Me.ListBox1.List(ii, 11)
suppliersSheet.Cells(ep, "A").Value = Me.ComboBox1.Value
suppliersSheet.Cells(ep, "B").Value = "المشتريات"
suppliersSheet.Cells(ep, "C").Value = Me.TextBox13.Value
suppliersSheet.Cells(ep, "D").Value = Me.TextBox14.Value
suppliersSheet.Cells(ep, "E").Value = Me.TextBox2.Value
suppliersSheet.Cells(ep, "F").Value = Me.TextBox3.Value
suppliersSheet.Cells(ep, "G").Value = Time
Else
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Me.ListBox1.List(ii, 0)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Me.ListBox1.List(ii, 1)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Me.ListBox1.List(ii, 2)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Me.ListBox1.List(ii, 3)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Me.ListBox1.List(ii, 4)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = Me.ListBox1.List(ii, 5)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 6) = Me.ListBox1.List(ii, 6)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 7) = Me.ListBox1.List(ii, 7)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 8) = Me.ListBox1.List(ii, 8)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 9) = Me.ListBox1.List(ii, 9)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 10) = Me.ListBox1.List(ii, 10)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 11) = Me.ListBox1.List(ii, 11)
'purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 12) = Me.ListBox1.List(ii, 12)
'purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 13) = Me.ListBox1.List(ii, 13)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 12) = Me.TextBox13.Value
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 13) = Me.TextBox14.Value
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 14) = Time
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Me.ListBox1.List(ii, 3)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Me.ListBox1.List(ii, 4)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Me.ListBox1.List(ii, 5)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Me.ListBox1.List(ii, 6)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Me.ListBox1.List(ii, 7)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = Me.ListBox1.List(ii, 8)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 6) = Me.ListBox1.List(ii, 9)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 7) = Me.ListBox1.List(ii, 10)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 8) = Me.ListBox1.List(ii, 11)
suppliersSheet.Cells(ep, "A").Value = Me.ComboBox1.Value
suppliersSheet.Cells(ep, "B").Value = "المشتريات"
suppliersSheet.Cells(ep, "C").Value = Me.TextBox13.Value
suppliersSheet.Cells(ep, "D").Value = Me.TextBox14.Value
suppliersSheet.Cells(ep, "E").Value = Me.TextBox2.Value
suppliersSheet.Cells(ep, "F").Value = Me.TextBox3.Value
suppliersSheet.Cells(ep, "G").Value = Time
'suppliersSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Me.TextBox13.Value
'suppliersSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Me.TextBox14.Value
'suppliersSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Me.ListBox1.List(ii, 1)
'suppliersSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = Me.ListBox1.List(ii, 2)

        End If
    End With
    Next ii
If j < d Then
ShortfallsSheet.Rows(ldelet).Delete
End If
'Me.TextBox5.Value = Me.ListBox1.List(ii, 4) + 1
Me.TextBox4.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.TextBox13.Value = ""
Me.TextBox14.Value = ""
MsgBox "تم اضافة البيانات بنجاح", vbInformation + vbMsgBoxRight + vbMsgBoxRtlReading, "تأكيد"
End Sub

 

رابط هذا التعليق
شارك
Ashraf Ehab

Ashraf Ehab

قام بنشر
  • الكاتب
قام بنشر

تم حل مشكلة مسح البيانات بنجاح وتبقي المشكلة الاولي وحل مشكلة المسح كانت كالاتي  

Dim qp As Integer
Dim rp As Integer
Dim wp As Integer
Dim ep As Integer
qp = purchasesSheet.Cells(Rows.Count, "D").End(xlUp).Row + 1
wp = ItemsSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
ep = suppliersSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
rp = Me.ListBox1.ListCount
    Dim t As Range
    Dim firsttAddress, ldelet As String
    Dim u As Integer
    Dim i As Integer
    Dim O As Integer
            Dim ii As Long
For ii = 1 To Me.ListBox1.ListCount - 1
ShortfallsSheet.Range("O1").Value = Me.ListBox1.List(ii, 3)
ldelet = ShortfallsSheet.Range("N1").Value


    u = Me.ListBox1.List(ii, 4)
    i = Me.ListBox1.List(ii, 6)


    With ItemsSheet.Range("B:B")
        Set t = .Find(u, LookIn:=xlValues)
        If Not t Is Nothing Then
            firsttAddress = t.Address
        O = ItemsSheet.Cells(t.Row, "D").Value
            O = O + i
        d = ItemsSheet.Cells(t.Row, "D").Value
            d = d
        j = ItemsSheet.Cells(t.Row, "J").Value
            j = j

purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Me.ListBox1.List(ii, 0)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Me.ListBox1.List(ii, 1)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Me.ListBox1.List(ii, 2)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Me.ListBox1.List(ii, 3)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Me.ListBox1.List(ii, 4)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = Me.ListBox1.List(ii, 5)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 6) = Me.ListBox1.List(ii, 6)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 7) = Me.ListBox1.List(ii, 7)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 8) = Me.ListBox1.List(ii, 8)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 9) = Me.ListBox1.List(ii, 9)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 10) = Me.ListBox1.List(ii, 10)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 11) = Me.ListBox1.List(ii, 11)
'purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 12) = Me.ListBox1.List(ii, 12)
'purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 13) = Me.ListBox1.List(ii, 13)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 12) = Me.TextBox13.Value
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 13) = Me.TextBox14.Value
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 14) = Time
ItemsSheet.Cells(t.Row, "D").Value = O 'Me.TextBox7.Value
ItemsSheet.Cells(t.Row, "G").Value = Me.ListBox1.List(ii, 9)
ItemsSheet.Cells(t.Row, "H").Value = Me.ListBox1.List(ii, 10)
ItemsSheet.Cells(t.Row, "I").Value = Me.ListBox1.List(ii, 11)
suppliersSheet.Cells(ep, "A").Value = Me.ComboBox1.Value
suppliersSheet.Cells(ep, "B").Value = "المشتريات"
suppliersSheet.Cells(ep, "C").Value = Me.TextBox13.Value
suppliersSheet.Cells(ep, "D").Value = Me.TextBox14.Value
suppliersSheet.Cells(ep, "E").Value = Me.TextBox2.Value
suppliersSheet.Cells(ep, "F").Value = Me.TextBox3.Value
suppliersSheet.Cells(ep, "G").Value = Time
Else
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Me.ListBox1.List(ii, 0)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Me.ListBox1.List(ii, 1)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Me.ListBox1.List(ii, 2)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Me.ListBox1.List(ii, 3)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Me.ListBox1.List(ii, 4)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = Me.ListBox1.List(ii, 5)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 6) = Me.ListBox1.List(ii, 6)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 7) = Me.ListBox1.List(ii, 7)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 8) = Me.ListBox1.List(ii, 8)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 9) = Me.ListBox1.List(ii, 9)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 10) = Me.ListBox1.List(ii, 10)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 11) = Me.ListBox1.List(ii, 11)
'purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 12) = Me.ListBox1.List(ii, 12)
'purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 13) = Me.ListBox1.List(ii, 13)
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 12) = Me.TextBox13.Value
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 13) = Me.TextBox14.Value
purchasesSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 14) = Time
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Me.ListBox1.List(ii, 3)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Me.ListBox1.List(ii, 4)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Me.ListBox1.List(ii, 5)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Me.ListBox1.List(ii, 6)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Me.ListBox1.List(ii, 7)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = Me.ListBox1.List(ii, 8)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 6) = Me.ListBox1.List(ii, 9)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 7) = Me.ListBox1.List(ii, 10)
ItemsSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 8) = Me.ListBox1.List(ii, 11)
suppliersSheet.Cells(ep, "A").Value = Me.ComboBox1.Value
suppliersSheet.Cells(ep, "B").Value = "المشتريات"
suppliersSheet.Cells(ep, "C").Value = Me.TextBox13.Value
suppliersSheet.Cells(ep, "D").Value = Me.TextBox14.Value
suppliersSheet.Cells(ep, "E").Value = Me.TextBox2.Value
suppliersSheet.Cells(ep, "F").Value = Me.TextBox3.Value
suppliersSheet.Cells(ep, "G").Value = Time
'suppliersSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Me.TextBox13.Value
'suppliersSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Me.TextBox14.Value
'suppliersSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Me.ListBox1.List(ii, 1)
'suppliersSheet.Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = Me.ListBox1.List(ii, 2)

        End If
    End With
    Next ii
If j < d Then
ShortfallsSheet.Cells.Rows(ldelet).Delete
End If
'Me.TextBox5.Value = Me.ListBox1.List(ii, 4) + 1
Me.TextBox4.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.TextBox13.Value = ""
Me.TextBox14.Value = ""
MsgBox "تم اضافة البيانات بنجاح", vbInformation + vbMsgBoxRight + vbMsgBoxRtlReading, "تأكيد"
End Sub

ارجو مساعدتي في المشكلة الاولي وانا احاول حلها وشكرا للجميع

رابط هذا التعليق
شارك
Ashraf Ehab

Ashraf Ehab

قام بنشر
  • الكاتب
قام بنشر

الحمد لله الذي وفقني للحل 

اولا احب اشكر الاستاذ محمد صالح لانه ساعدني كثيرا بهذه الفكرة 

ثانيا تم حل المشكلة بالكود الاتي 

  

Dim sc As Integer
sc = customersSheet.Range("A10000").End(xlUp).Row + 1
Dim c, cv As Range
    Dim firstAddress, nfe As String
    Dim f, nf As Integer
    Dim x As Integer
    Dim Y As Integer
    Dim last, last1, lse As Integer
last = SelesSheet.Range("A10000").End(xlUp).Row + 1
last1 = ShortfallsSheet.Range("A10000").End(xlUp).Row + 1
For i = 0 To ListBox1.ListCount - 1
ShortfallsSheet.Range("O1").Value = Me.ListBox1.List(i, 4)
lse = ShortfallsSheet.Range("N1").Value
nfe = Me.ListBox1.List(i, 4)


       f = Me.ListBox1.List(i, 3)
       x = Me.ListBox1.List(i, 1)
       With ItemsSheet.Range("B:B")
        Set c = .Find(f, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
        Y = ItemsSheet.Cells(c.Row, "D").Value
            Y = Y - x
        a = ItemsSheet.Cells(c.Row, "A").Value
            a = a
        b = ItemsSheet.Cells(c.Row, "B").Value
            b = b
        cc = ItemsSheet.Cells(c.Row, "C").Value
            cc = cc
        d = ItemsSheet.Cells(c.Row, "D").Value
            d = d
        e = ItemsSheet.Cells(c.Row, "E").Value
            e = e
        ff = ItemsSheet.Cells(c.Row, "F").Value
            ff = ff
        g = ItemsSheet.Cells(c.Row, "G").Value
            gg = gg
        j = ItemsSheet.Cells(c.Row, "J").Value
            j = j
SelesSheet.Cells(last, "I").Value = Me.ListBox1.List(i, 4)
SelesSheet.Cells(last, "J").Value = Me.ListBox1.List(i, 3)
SelesSheet.Cells(last, "K").Value = Me.ListBox1.List(i, 2)
SelesSheet.Cells(last, "L").Value = Me.ListBox1.List(i, 1)
SelesSheet.Cells(last, "M").Value = Me.ListBox1.List(i, 0)
ItemsSheet.Cells(c.Row, "D").Value = Y
last = last + 1
End If
End With
Next i
 Sheets("Shortfalls").Select
For Each hh In Range("a1:a" & Range("a10000").End(xlUp).Row)
If hh Like nfe & "*" Then
ShortfallsSheet.Cells(lse, "D").Value = d - x

ElseIf d <= j Then
Exit For
ShortfallsSheet.Cells(last1, "A").Value = a
ShortfallsSheet.Cells(last1, "B").Value = b
ShortfallsSheet.Cells(last1, "C").Value = cc
ShortfallsSheet.Cells(last1, "D").Value = d - x
ShortfallsSheet.Cells(last1, "E").Value = e
ShortfallsSheet.Cells(last1, "F").Value = ff
ShortfallsSheet.Cells(last1, "G").Value = g
last1 = last1 + 1
End If
Next
    Dim ls1 As Integer
    Dim ls2 As Integer
ls1 = SelesSheet.Range("A10000").End(xlUp).Row + 1
ls2 = SelesSheet.Range("I10000").End(xlUp).Row
For s = ls1 To ls2
SelesSheet.Cells(ls1, "A").Value = Me.TextBox1.Value
SelesSheet.Cells(ls1, "B").Value = Me.TextBox2.Value
SelesSheet.Cells(ls1, "C").Value = Me.TextBox3.Value
SelesSheet.Cells(ls1, "D").Value = Me.ComboBox1.Value
SelesSheet.Cells(ls1, "E").Value = Me.TextBox13.Value
SelesSheet.Cells(ls1, "F").Value = Me.TextBox11.Value
SelesSheet.Cells(ls1, "G").Value = Me.TextBox12.Value
SelesSheet.Cells(ls1, "H").Value = Me.TextBox14.Value
SelesSheet.Cells(ls1, "N").Value = Time
customersSheet.Cells(sc, "A").Value = Me.ComboBox1.Value
customersSheet.Cells(sc, "C").Value = Me.TextBox11.Value
customersSheet.Cells(sc, "B").Value = "ãÈíÚÇÊ"
customersSheet.Cells(sc, "D").Value = Me.TextBox12.Value
customersSheet.Cells(sc, "E").Value = Me.TextBox2.Value
customersSheet.Cells(sc, "F").Value = Me.TextBox3.Value
customersSheet.Cells(sc, "G").Value = Time
ls1 = ls1 + 1

Next s
MsgBox "Êã ÇÖÇÝÉ ÇáÈíÇäÇÊ ÈäÌÇÍ", vbInformation + vbMsgBoxRight + vbMsgBoxRtlReading, "ÊÇßíÏ"

اشكر الاستاذ محمد صالح مرة اخري علي نصيحتة 

رابط هذا التعليق
شارك
زائر
هذا الموضوع مغلق.
 مشاركة
الذهاب الي قائمه المواضيع

  • تصفح هذا الموضوع مؤخراً   0 اعضاء متواجدين الان

    • لايوجد اعضاء مسجلون يتصفحون هذه الصفحه
×
×
  • اضف...

Important Information

  I accept