Create Inventory Stock Report Form Excel VBA

Create Inventory Stock Report Form Excel VBA

A Video Creating inventory report Sales Qty Purchase Qty Balance Qty And Stock Amount With in One click support ur small business Thank you razakmcr

एक वीडियो बनाना इन्वेंट्री रिपोर्ट, बिक्री मात्रा खरीद मात्रा संतुलन मात्रा और स्टॉक राशि एक क्लिक के साथ उर छोटे व्यवसाय का समर्थन करती है

धन्यवाद razakmcr

فيديو إنشاء تقرير مخزون المبيعات الكمية الكمية الشراء الكمية الكمية الكمية مع بنقرة واحدة دعم الأعمال الصغيرة الخاصة بك شكرا لك razakmcr

Watch Video

VBA Code

CommandButton1

Private Sub CommandButton1_Click() Dim i As Long Me.ListBox1.Clear Me.ListBox1.AddItem Me.ListBox1.List(0, 0) = "item Name" Me.ListBox1.List(0, 1) = "item No" Me.ListBox1.List(0, 2) = "Purchase Qty" Me.ListBox1.List(0, 3) = "Purchase Price" Me.ListBox1.List(0, 4) = "Sales Qty" Me.ListBox1.List(0, 5) = "Sales Pirce" Me.ListBox1.List(0, 6) = "Balance Qty" Me.ListBox1.List(0, 7) = "Stock Amount" For i = 1 To Sheet3.Range("A1000000").End(xlUp).Row Me.ListBox1.AddItem Sheet3.Cells(i, "A") 'Sheet3("inventory") Column(A) Item Name 'Listbox item fill Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet3.Cells(i, "B") 'Column(B)Item Code Next i '__________ Dim C As Integer Dim X As Long Dim sum, sum1, sum2, sum3 As Double With Me.ListBox1 For C = 1 To .ListCount - 1 For X = 2 To Sheet2.Range("A1000000").End(xlUp).Row 'Sheet2("Database") 'Column(G) Itemcode 'Column(D) Voucher If Val(.List(C, 1)) = Sheet2.Cells(X, "G") And Sheet2.Cells(X, "D") = "Purchase" Then 'Purchase Qty sum = sum + Sheet2.Cells(X, "H") 'Column(H) Qty 'Purchase Amount sum1 = sum1 + Sheet2.Cells(X, "J") 'Column(J) Amount .List(C, 2) = sum .List(C, 3) = sum1 'Column(G) Itemcode 'Column(D) Voucher ElseIf Val(.List(C, 1)) = Sheet2.Cells(X, "G") And Sheet2.Cells(X, "D") = "Sales" Then 'Sales Qty sum2 = sum2 + Sheet2.Cells(X, "H") 'Column(H) Qty 'Sales Amount sum3 = sum3 + Sheet2.Cells(X, "J") 'Column(J) Amount .List(C, 4) = sum2 .List(C, 5) = sum3 End If Next X sum = 0 sum1 = 0 sum2 = 0 sum3 = 0 '______________ 'Balance Qty 'Purchase Qty - 'Sales Qty .List(C, 6) = Val(.List(C, 2)) - Val(.List(C, 4)) If Val(.List(C, 6)) >= 1 Then 'Balance Amount 'Purchase Amount / Purchase Qty * Balance Qty .List(C, 7) = Val(.List(C, 3)) / Val(.List(C, 2)) * Val(.List(C, 6)) End If Next C .Selected(0) = True End With

End Sub

CommandButton2

Private Sub CommandButton2_Click()

Me.ListBox1.Clear Me.ListBox1.AddItem Me.ListBox1.List(0, 0) = "Date" Me.ListBox1.List(0, 1) = "Invoice No" Me.ListBox1.List(0, 2) = "Voucher" Me.ListBox1.List(0, 3) = "Ledeger" Me.ListBox1.List(0, 4) = "Item Name" Me.ListBox1.List(0, 5) = "Item Code" Me.ListBox1.List(0, 6) = "Qty" Me.ListBox1.List(0, 7) = "Price" Me.ListBox1.List(0, 8) = "Total" Dim i As Long For i = 2 To Sheet2.Range("A1000000").End(xlUp).Row 'Sheet2("Database") 'Column(G) ItemCode If Sheet2.Cells(i, "G") = Val(Me.TextBox1) Then With Me.ListBox1 .AddItem For X = 1 To 9 'Loop 1 to 9 Column Listbox And Worksheet .List(.ListCount - 1, X - 1) = Sheet2.Cells(i, X + 1) Next X End With End If Next i Me.ListBox1.Selected(0) = True Me.ListBox1.ColumnCount = 9 '____________________ Dim C As Integer Dim sum, sum1, sum2, sum3 As Double With Me.ListBox1 For C = 1 To Me.ListBox1.ListCount - 1 On Error Resume Next 'sum On Listbox 'Purchase Qty If .List(C, 2) = "Purchase" Then sum = sum + Val(.List(C, 6)) End If 'Sales Qty If .List(C, 2) = "Sales" Then sum1 = sum1 + Val(.List(C, 6)) End If 'Purchase Amount If .List(C, 2) = "Purchase" Then sum2 = sum2 + Val(.List(C, 8)) End If 'Sales Amount If .List(C, 2) = "Sales" Then sum3 = sum3 + Val(.List(C, 8)) End If Next C End With '_______________ Me.ListBox1.AddItem "____________________" Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "_____________________" Me.ListBox1.AddItem "Purchase Qty" Me.ListBox1.List(ListBox1.ListCount - 1, 1) = sum Me.ListBox1.AddItem "Sales Qty" Me.ListBox1.List(ListBox1.ListCount - 1, 1) = sum1 Me.ListBox1.AddItem "Balance Qty" Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Val(sum - sum1) Me.ListBox1.AddItem "Purchase Amount" Me.ListBox1.List(ListBox1.ListCount - 1, 1) = sum2 Me.ListBox1.AddItem "Sales Amount" Me.ListBox1.List(ListBox1.ListCount - 1, 1) = sum3 End Sub Private Sub ListBox1_Click() On Error Resume Next Me.TextBox1 = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)

End Sub

Textbox1

Private Sub TextBox1_Change() If IsNumeric(Me.TextBox1) Then Me.CommandButton2.Enabled = True Else Me.CommandButton2.Enabled = False End If End Sub