Track movement stock in listbox on userform by copy amounts from previous row to next row

A

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
612
Office Version
  1. 2019
Hi experts
combobox1 should match with column B for each sheet then should brings data and populate in listbox1 :
1-in column (1) for listbox1 will show sheet name and brings data for old date across sheets .
2- in column (2)for listbox1 will brings INVOICE.N from each sheet is existed in column C based on adjacent sheet name in column (1) for listbox1
3- in column (3)for listbox1 will brings DATE from each sheet is existed in column A based on adjacent sheet name in column (1) for listbox1
4- in column (4) for listbox1 will brings CUSTOMER from each sheet is existed in column D based on adjacent sheet name in column (1) for listbox1.
5- in column (5) for row2 in listbox1 will brings QTY from STOCK sheet in Column (C) .
6-in column (6) for listbox1 will brings UNIT PRICE from STOCK sheet in Column (D) .
7- in column(7) will brings QTY from each sheet in column(E) based on adjacent sheet name in column (1) for listbox1
8- in column(8) will brings UNIT PRICE from each sheet in column(F) based on adjacent sheet name in column (1) for listbox1.
9- in column(9) will calculation QTY based on sheet name , if sheet name in column (1) for listbox1 is
BUYING then the column(9)=column(5) +column(7)
if sheet name in column (1)for listbox1 is
SELLING then the column(9)=column(5) - column(7), if sheet name in column (1) for listbox1 is BUYING RETURNS then the column(9)=column(5) - column(7) , if sheet name in column (1) for listbox1 is
SELLING RETURNS then the column(9)=column(5) +column(7)
10-column(10) should be the same column(8)
next row(3) depends on row2 then will move numeric vales from row2 to row 3 in listbox1
the values are existed in column 5,6 for next row will depends on previous row in columns 9,10
in column( 9) listbox1 for in row2 will copy to column( 5 )in row3 for listbox1 ,
in column( 8) in listbox1 for row2 will copy to column(6 )in row3 for listbox1 ,
in columns( 7,8) in listbox1 will brings QTY,PRICE from sheet name is matched with column (1) for row3 in listbox1 ,
in column( 9 ) will calculation QTY for row3(CHECK POINT 8) .
in column( 10 ) for row3 (check point9)
without forgetting add headers in row1 in listbox1


my data in each sheet will be
1BRANDS V3.xlsm
ABCDEFG
1DATEBRANDINVOICE.NCUSTOMERQTYUNIT PRICEBALANCE
201/01/2024BS 195R15C R623 JAPSDF1100CRTE10040.00300.0012,000.00
301/01/2024FS 195R15C R623 THISDF1100CRTE10050.00250.0012,500.00
401/01/2024DT 385/65R22.5 DS53 THISDF1100CRTE10020.001,600.0032,000.00
501/01/2024BS 315/80R22.5 R184 JAPSDF1100CRTE10030.001,600.0048,000.00
601/01/2024BS 315/80R22.5 R184 THISDF1101CRTE10140.001,650.0066,000.00
701/01/2024BS 1200R20 G580 JAPSDF1101CRTE101500.001,800.00900,000.00
801/01/2024BS 750R16 R230 JAPSDF1101CRTE101200.00750.00150,000.00
901/01/2024BS 1200R24 G582 JAPSDF1101CRTE101500.002,200.001,100,000.00
1002/02/2024BS 1200R20 G580 THISDF1102CRTE102500.001,825.00912,500.00
1102/02/2024BS 1200R20 R187 THISDF1102CRTE102600.001,700.001,020,000.00
1202/02/2024BS 1200R20 R187 JAPSDF1102CRTE102500.001,650.00825,000.00
1302/02/2024BS 1200R24 G580 JAPSDF1102CRTE102700.002,350.001,645,000.00
1402/02/2024BS 700R16 R230 JAPSDF1102CRTE102340.00650.00221,000.00
1502/02/2024BS 750R16 VSJ JAPSDF1102CRTE102400.00750.00300,000.00
1602/02/2024BS 1200R20 G580 JAPSDF1102CRTE102400.001,885.00754,000.00
1702/02/2024BS 750R16 VSJ JAPSDF1102CRTE102600.00770.00462,000.00
1802/02/2024BS 750R16 VSJ JAPSDF1103CRTE103400.00800.00320,000.00
1902/02/2024BS 1200R20 G580 JAPSDF1103CRTE103500.001,760.00880,000.00
BUYING
Cell Formulas
RangeFormula
G2:G19G2=E2*F2




1BRANDS V3.xlsm
ABCDEFG
1DATEBRANDINVOICE.NCUSTOMERQTYUNIT PRICEBALANCE
201/11/2024BS 1200R20 G580 JAPBST-100CMM100200.002,000.00400,000.00
301/11/2024BS 1200R20 G580 THIBST-100CMM100100.001,990.00199,000.00
401/11/2024BS 1200R20 R187 THIBST-100CMM10050.002,100.00105,000.00
501/11/2024BS 1200R20 R187 JAPBST-100CMM100100.002,200.00220,000.00
601/11/2024BS 1200R24 G580 JAPBST-100CMM100100.002,500.00250,000.00
701/11/2024BS 1200R24 G582 JAPBST-101CMM101120.002,600.00312,000.00
802/12/2024BS 750R16 R230 JAPBST-101CMM101100.00750.0075,000.00
902/12/2024BS 700R16 R230 JAPBST-101CMM101120.00730.0087,600.00
1002/12/2024BS 750R16 VSJ JAPBST-101CMM101100.00900.0090,000.00
1102/12/2024BS 1200R20 G580 JAPBST-102CMM102120.002,100.00252,000.00
1202/12/2024BS 750R16 VSJ JAPBST-102CMM10250.00920.0046,000.00
1302/12/2024BS 1200R20 G580 JAPBST-103CMM103120.002,120.00254,400.00
1402/12/2024BS 1200R20 G580 JAPBST-104CMM104100.002,110.00211,000.00
1502/12/2024BS 1200R20 R187 JAPBST-104CMM104200.002,130.00426,000.00
1602/12/2024BS 1200R24 G580 JAPBST-104CMM10410.002,450.0024,500.00
1702/12/2024BS 1200R20 G580 THIBST-105CMM105130.001,895.00246,350.00
1802/12/2024BS 1200R20 G580 JAPBST-105CMM10517.002,140.0036,380.00
SELLING
Cell Formulas
RangeFormula
G2:G18G2=E2*F2



1BRANDS V3.xlsm
ABCDEFG
1DATEBRANDINVOICE.NCUSTOMERQTYUNIT PRICEBALANCE
201/20/2024DT 385/65R22.5 DS53 THIDDT-100DDC-1005.001,600.008,000.00
301/20/2024BS 315/80R22.5 R184 JAPDDT-100DDC-1005.001,600.008,000.00
401/20/2024BS 315/80R22.5 R184 THIDDT-100DDC-1004.001,650.006,600.00
501/20/2024DT 385/65R22.5 DS53 THIDDT-101DDC-1011.001,600.001,600.00
601/20/2024BS 315/80R22.5 R184 JAPDDT-101DDC-1012.001,600.003,200.00
701/20/2024BS 315/80R22.5 R184 THIDDT-101DDC-1011.001,650.001,650.00
801/20/2024BS 1200R20 G580 THIDDT-101CRTE10220.001,825.0036,500.00
901/20/2024BS 1200R20 R187 THIDDT-101CRTE1024.001,700.006,800.00
1002/21/2024BS 1200R20 R187 JAPDDT-101CRTE10210.001,650.0016,500.00
1102/21/2024BS 1200R20 G580 JAPDDT-101CRTE10220.002,350.0047,000.00
1202/21/2024BS 700R16 R230 JAPDDT-101CRTE10220.00650.0013,000.00
1302/21/2024BS 750R16 VSJ JAPDDT-101CRTE10215.00750.0011,250.00
BUYING RETURNS
Cell Formulas
RangeFormula
G2:G13G2=E2*F2




1BRANDS V3.xlsm
ABCDEFG
1DATEBRANDINVOICE.NCUSTOMERQTYUNIT PRICEBALANCE
201/11/2024BS 1200R20 G580 JAPFST-212FSST-12310.002,000.0020,000.00
301/11/2024BS 1200R20 G580 THIFST-212FSST-12310.001,990.0019,900.00
402/22/2024BS 700R16 R230 JAPFST-212FSST-12312.00730.008,760.00
502/22/2024BS 750R16 VSJ JAPFST-212FSST-12310.00900.009,000.00
602/22/2024BS 750R16 R230 JAPFST-212FSST-12310.00750.007,500.00
702/22/2024BS 1200R20 R187 THIFST-212FSST-1234.002,100.008,400.00
802/22/2024BS 1200R20 R187 JAPFST-212FSST-1234.002,200.008,800.00
902/22/2024BS 1200R24 G580 JAPFST-212FSST-1234.002,500.0010,000.00
1002/22/2024BS 1200R20 G580 JAPFST-212FSST-1232.002,600.005,200.00
SELLING RETURNS
Cell Formulas
RangeFormula
G2:G10G2=E2*F2



1BRANDS V3.xlsm
ABCDE
1ITEMBRANDQTYUNIT PRICEQTY
21BS 195R15C R623 JAP450.00180.0081,000.00
32FS 195R15C R623 THI700.00190.00133,000.00
43DT 385/65R22.5 DS53 THI800.001,400.001,120,000.00
54BS 315/80R22.5 R184 JAP900.001,250.001,125,000.00
65BS 315/80R22.5 R184 THI1,000.001,225.001,225,000.00
76BS 1200R20 G580 JAP200.002,035.00407,000.00
87BS 750R16 R230 JAP120.00550.0066,000.00
98BS 1200R24 G582 JAP60.002,200.00132,000.00
109BS 1200R20 G580 THI50.002,000.00100,000.00
1110BS 1200R20 R187 THI40.001,770.0070,800.00
1211BS 1200R20 R187 JAP30.001,780.0053,400.00
1312BS 1200R24 G580 JAP20.002,125.0042,500.00
1413BS 700R16 R230 JAP25.00450.0011,250.00
1514BS 750R16 VSJ JAP45.00650.0029,250.00
1615BS 650R16 R230 JAP80.00500.0040,000.00
1716BS 205/70R15C R623 THI90.00400.0036,000.00
1817BS 195/65R15 TG90 JAP100.00205.0020,500.00
1918BS 225/70R15C R623 JAP200.00350.0070,000.00
STOCK
Cell Formulas
RangeFormula
E2:E19E2=C2*D2



so when search for BRAND in combobox1 and match with column B across sheet will be
BR1.PNG



when select combobox1 the BRAND and write two dates in textbox1,textbox2 then will show data for brand based on dates.
BR2.PNG

when clear dates then should populate based on combobox1 if it's filled and if combobox1 is filled and bot textboxes are filled then will clear textbox until fill combobox1 and when bot textboxes and combobox are empty then don't show any thing in listbox when run the form .
I have data 8800 rows for each sheet as maximum .
I know this project is complex , if any detail is not clear just ask me .
thanks
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Hi Abdo:

I think I solved everything. But I'm not sure if the dates will work, my tests work with the dd/mm/yyyy format, but I see that in your sheet and in the textbox you are going to put the date format: mm/dd/yyyy.
1729797246471.png

But try and tell me. Or change the dates on your sheet to dd/mm/yyyy and try.

Put all the code in the form. The code also loads the combobox1.

IMPORTANT:
The code requires putting the data in the sheet, let's say in the stock sheet, in some available columns, let's say in columns P to W to put the data and sort it by date.​
If it is not possible, the stock sheet can be adjusted to another one.​

VBA Code: 
Option Explicit

Dim a As Variant
Dim dicStock As Object

Sub FilterData()
  Dim cmb1 As Variant, txt1 As String, txt2 As String
  Dim i As Long, j As Long, k As Long, k2 As Long, m As Long, LItm As Long, n As Long
  Dim b As Variant, c As Variant, itm As Variant, arr As Variant
  Dim tot As Double, Qty As Double, Pri As Double
  Dim bExclude As Boolean
  Const frm As String = "#,##0.00;-#,##0.00;0.00;0.00"

  ListBox1.Clear
  tot = 0
  ReDim b(1 To UBound(a, 1), 1 To 10)

  k = 1
  b(k, 1) = "Type of exchange perm"
  b(k, 2) = "INVOICE.N"
  b(k, 3) = "DATE"
  b(k, 4) = "CUSTOMER"
  b(k, 5) = "PREVIOUS QTY"
  b(k, 6) = "PREVIOUS PRICE"
  b(k, 7) = "QTY"
  b(k, 8) = "UNIT PRICE"
  b(k, 9) = "CURRENT QTY"
  b(k, 10) = "CURRENT PRICE"
 
  'If dicStock.exists(cmb1) Then
    Qty = Split(dicStock(ComboBox1.Value), "|")(0)     'stock C
    Pri = Split(dicStock(ComboBox1.Value), "|")(1)     'stock D

  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cmb1 = a(i, 3) Else cmb1 = ComboBox1.Value

    If TextBox1.Value <> "" And TextBox2.Value <> "" Then
      txt1 = checkDate(TextBox1.Value, a(i, 2))
      txt2 = checkDate(TextBox2.Value, a(i, 2))
    Else
      txt1 = a(i, 2)
      txt2 = a(i, 2)
    End If
   
    If a(i, 3) = cmb1 And _
       a(i, 2) >= CDate(txt1) And a(i, 2) <= CDate(txt2) Then

      k = k + 1
      b(k, 1) = a(i, 1)                                       'sheet
      b(k, 2) = a(i, 4)                                       'invoice
      b(k, 3) = Format(Day(a(i, 2)), "00") & "/" & Format(Month(a(i, 2)), "00") & "/" & Year(a(i, 2))
      b(k, 4) = a(i, 5)                                       'customer
      b(k, 5) = Format(Qty, frm)                              'curr qty
      b(k, 6) = Format(Pri, frm)                              'curr price
      If a(i, 1) = "BUYING" Or a(i, 1) = "SELLING RETURNS" Then Qty = Qty + a(i, 6) Else Qty = Qty - a(i, 6)
      Pri = a(i, 7)
      b(k, 7) = Format(a(i, 6), frm)                          'Qty
      b(k, 8) = Format(a(i, 7), frm)                          'unit price
      b(k, 9) = Format(Qty, frm)                              'curr qty
      b(k, 10) = Format(a(i, 7), frm)                         'curr price
    End If
  Next

  If k > 0 Then
    ReDim c(1 To k, 1 To UBound(b, 2))
    For i = 1 To k
      For j = 1 To UBound(b, 2)
        c(i, j) = b(i, j)
      Next
    Next

    ListBox1.List = c
  End If
End Sub

Function checkDate(valText, fec)
  If valText = "" Or Not IsDate(valText) Or Len(valText) <> 10 Or _
     Mid(valText, 3, 1) <> "/" Or Mid(valText, 6, 1) <> "/" Then
    checkDate = fec
  Else
    checkDate = valText
  End If
End Function

Private Sub ComboBox1_Change()
  If ComboBox1.ListIndex = -1 Or ComboBox1.Value = "" Then
    ListBox1.Clear
    TextBox1.Value = ""
    TextBox2.Value = ""
    Exit Sub
  End If
  Call FilterData
End Sub
Private Sub TextBox1_Change()
  With TextBox1
    If .Value = "" Then Exit Sub
    If ComboBox1.ListIndex = -1 Or ComboBox1.Value = "" Then
      MsgBox "Enter Brand"
      .Value = ""
      ComboBox1.SetFocus
      Exit Sub
    End If
   
    If Not IsDate(.Value) Or Len(.Value) <> 10 Or _
     Mid(.Value, 3, 1) <> "/" Or Mid(.Value, 6, 1) <> "/" Then
    Else
      If TextBox2.Value <> "" Then
        Call FilterData
      End If
    End If
  End With
End Sub

Private Sub TextBox2_Change()
  With TextBox2
    If .Value = "" Then Exit Sub
    If ComboBox1.ListIndex = -1 Or ComboBox1.Value = "" Then
      MsgBox "Enter Brand"
      .Value = ""
      ComboBox1.SetFocus
      Exit Sub
    End If
   
    If Not IsDate(.Value) Or Len(.Value) <> 10 Or _
     Mid(.Value, 3, 1) <> "/" Or Mid(.Value, 6, 1) <> "/" Then
    Else
      If TextBox1.Value <> "" Then
        Call FilterData
      End If
    End If
  End With
End Sub

Private Sub UserForm_Activate()
  Dim itm As Variant, b As Variant, arr As Variant
  Dim lr As Long, i As Long, j As Long, k As Long
  Dim dic As Object
  Dim sh1 As Worksheet
 
  Set sh1 = Sheets("STOCK")
  Set dic = CreateObject("Scripting.Dictionary")
  Set dicStock = CreateObject("Scripting.Dictionary")

  arr = Array("BUYING", "SELLING", "BUYING RETURNS", "SELLING RETURNS")
  For Each itm In arr
    lr = lr + Sheets(itm).Range("A" & Rows.Count).End(3).Row - 1
  Next
  ReDim a(1 To lr, 1 To 8)

  For Each itm In arr
    b = Sheets(itm).Range("A2", Sheets(itm).Range("G" & Rows.Count).End(3)).Value
    For i = 1 To UBound(b, 1)
      k = k + 1
      dic(b(i, 2)) = Empty
      a(k, 1) = itm
      For j = 1 To UBound(b, 2)
        a(k, j + 1) = b(i, j)
      Next
    Next
  Next
 
  For i = 2 To sh1.Range("B" & Rows.Count).End(3).Row
    dicStock(sh1.Range("B" & i).Value) = sh1.Range("C" & i).Value & "|" & sh1.Range("D" & i).Value
  Next

  Application.ScreenUpdating = False
    'load combobox1
    With sh1.Range("P2").Resize(dic.Count)
      .Value = Application.Transpose(dic.keys)
      .Sort .Cells(1), xlAscending, Header:=xlNo
      ComboBox1.List = .Value
      .ClearContents
    End With
 
    'load variable matrix 'a'
    With sh1.Range("P2").Resize(UBound(a, 1), UBound(a, 2))
      .Value = a
      .Sort .Cells(1, 2), xlAscending, Header:=xlNo
      a = .Value
      .ClearContents
    End With
  Application.ScreenUpdating = True

  With ListBox1
    .RowSource = ""
    .ColumnCount = 10
  End With

End Sub

😅 😇
 
Upvote 0
Awesome !
I read your instruction and notes all of things are good.
but this
when clear dates then should populate based on combobox1
Click to expand...
doesn't work as I want . when clear textbox2,1 will not populate data based on combobox1.
may you fix it,please?
 
Upvote 0
Try:

VBA Code: 
Dim a As Variant
Dim dicStock As Object

Sub FilterData()
  Dim cmb1 As Variant, txt1 As String, txt2 As String
  Dim i As Long, j As Long, k As Long, k2 As Long, m As Long, LItm As Long, n As Long
  Dim b As Variant, c As Variant, itm As Variant, arr As Variant
  Dim tot As Double, Qty As Double, Pri As Double
  Dim bExclude As Boolean
  Const frm As String = "#,##0.00;-#,##0.00;0.00;0.00"

  ListBox1.Clear
  tot = 0
  ReDim b(1 To UBound(a, 1), 1 To 10)

  k = 1
  b(k, 1) = "Type of exchange perm"
  b(k, 2) = "INVOICE.N"
  b(k, 3) = "DATE"
  b(k, 4) = "CUSTOMER"
  b(k, 5) = "PREVIOUS QTY"
  b(k, 6) = "PREVIOUS PRICE"
  b(k, 7) = "QTY"
  b(k, 8) = "UNIT PRICE"
  b(k, 9) = "CURRENT QTY"
  b(k, 10) = "CURRENT PRICE"
  
  'If dicStock.exists(cmb1) Then
    Qty = Split(dicStock(ComboBox1.Value), "|")(0)     'stock C
    Pri = Split(dicStock(ComboBox1.Value), "|")(1)     'stock D

  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cmb1 = a(i, 3) Else cmb1 = ComboBox1.Value

    If TextBox1.Value <> "" And TextBox2.Value <> "" Then
      txt1 = checkDate(TextBox1.Value, a(i, 2))
      txt2 = checkDate(TextBox2.Value, a(i, 2))
    Else
      txt1 = a(i, 2)
      txt2 = a(i, 2)
    End If
    
    If a(i, 3) = cmb1 And _
       a(i, 2) >= CDate(txt1) And a(i, 2) <= CDate(txt2) Then

      k = k + 1
      b(k, 1) = a(i, 1)                                       'sheet
      b(k, 2) = a(i, 4)                                       'invoice
      b(k, 3) = Format(Day(a(i, 2)), "00") & "/" & Format(Month(a(i, 2)), "00") & "/" & Year(a(i, 2))
      b(k, 4) = a(i, 5)                                       'customer
      b(k, 5) = Format(Qty, frm)                              'curr qty
      b(k, 6) = Format(Pri, frm)                              'curr price
      If a(i, 1) = "BUYING" Or a(i, 1) = "SELLING RETURNS" Then Qty = Qty + a(i, 6) Else Qty = Qty - a(i, 6)
      Pri = a(i, 7)
      b(k, 7) = Format(a(i, 6), frm)                          'Qty
      b(k, 8) = Format(a(i, 7), frm)                          'unit price
      b(k, 9) = Format(Qty, frm)                              'curr qty
      b(k, 10) = Format(a(i, 7), frm)                         'curr price
    End If
  Next

  If k > 0 Then
    ReDim c(1 To k, 1 To UBound(b, 2))
    For i = 1 To k
      For j = 1 To UBound(b, 2)
        c(i, j) = b(i, j)
      Next
    Next

    ListBox1.List = c
  End If
End Sub

Function checkDate(valText, fec)
  If valText = "" Or Not IsDate(valText) Or Len(valText) <> 10 Or _
     Mid(valText, 3, 1) <> "/" Or Mid(valText, 6, 1) <> "/" Then
    checkDate = fec
  Else
    checkDate = valText
  End If
End Function

Private Sub ComboBox1_Change()
  If ComboBox1.ListIndex = -1 Or ComboBox1.Value = "" Then
    ListBox1.Clear
    TextBox1.Value = ""
    TextBox2.Value = ""
    Exit Sub
  End If
  Call FilterData
End Sub

Private Sub TextBox1_Change()
  With TextBox1
    If .Value = "" Then
      If TextBox2.Value = "" And ComboBox1.ListIndex > -1 Then
        Call FilterData
        Exit Sub
      End If
    End If
    If ComboBox1.ListIndex = -1 Or ComboBox1.Value = "" Then
      MsgBox "Enter Brand"
      .Value = ""
      ComboBox1.SetFocus
      Exit Sub
    End If
    
    If Not IsDate(.Value) Or Len(.Value) <> 10 Or _
     Mid(.Value, 3, 1) <> "/" Or Mid(.Value, 6, 1) <> "/" Then
    Else
      If TextBox2.Value <> "" Then
        Call FilterData
      End If
    End If
  End With
End Sub

Private Sub TextBox2_Change()
  With TextBox2
    If .Value = "" Then
      If TextBox1.Value = "" And ComboBox1.ListIndex > -1 Then
        Call FilterData
        Exit Sub
      End If
    End If
    
    If ComboBox1.ListIndex = -1 Or ComboBox1.Value = "" Then
      MsgBox "Enter Brand"
      .Value = ""
      ComboBox1.SetFocus
      Exit Sub
    End If
    
    If Not IsDate(.Value) Or Len(.Value) <> 10 Or _
     Mid(.Value, 3, 1) <> "/" Or Mid(.Value, 6, 1) <> "/" Then
    Else
      If TextBox1.Value <> "" Then
        Call FilterData
      End If
    End If
  End With
End Sub

Private Sub UserForm_Activate()
  Dim itm As Variant, b As Variant, arr As Variant
  Dim lr As Long, i As Long, j As Long, k As Long
  Dim dic As Object
  Dim sh1 As Worksheet
  
  Set sh1 = Sheets("STOCK")
  Set dic = CreateObject("Scripting.Dictionary")
  Set dicStock = CreateObject("Scripting.Dictionary")

  arr = Array("BUYING", "SELLING", "BUYING RETURNS", "SELLING RETURNS")
  For Each itm In arr
    lr = lr + Sheets(itm).Range("A" & Rows.Count).End(3).Row - 1
  Next
  ReDim a(1 To lr, 1 To 8)

  For Each itm In arr
    b = Sheets(itm).Range("A2", Sheets(itm).Range("G" & Rows.Count).End(3)).Value
    For i = 1 To UBound(b, 1)
      k = k + 1
      dic(b(i, 2)) = Empty
      a(k, 1) = itm
      For j = 1 To UBound(b, 2)
        a(k, j + 1) = b(i, j)
      Next
    Next
  Next
  
  For i = 2 To sh1.Range("B" & Rows.Count).End(3).Row
    dicStock(sh1.Range("B" & i).Value) = sh1.Range("C" & i).Value & "|" & sh1.Range("D" & i).Value
  Next

  Application.ScreenUpdating = False
    'load combobox1
    With sh1.Range("P2").Resize(dic.Count)
      .Value = Application.Transpose(dic.keys)
      .Sort .Cells(1), xlAscending, Header:=xlNo
      ComboBox1.List = .Value
      .ClearContents
    End With
  
    'load variable matrix 'a'
    With sh1.Range("P2").Resize(UBound(a, 1), UBound(a, 2))
      .Value = a
      .Sort .Cells(1, 2), xlAscending, Header:=xlNo
      a = .Value
      .ClearContents
    End With
  Application.ScreenUpdating = True

  With ListBox1
    .RowSource = ""
    .ColumnCount = 10
  End With
End Sub

😇
 
Upvote 0
Solution
thanks
I have question :
this case is really strange!
why when I change sheets names inside the code based what I have in the sheet will show minus values in CURRENT QTY column and doesn't calculate correctly?!
 
Upvote 0
Obviously you don't do it well.
What names do your sheets have?
According to the OP, which name is wrong in my code?
 
Upvote 0
Also change the sheet names on this line:


Rich (BB code): 
If a(i, 1) = "BUYING" Or a(i, 1) = "SELLING RETURNS" Then Qty = Qty + a(i, 6) Else Qty = Qty - a(i, 6)


😇
 
Upvote 0
DanteAmor said:
Also change the sheet names on this line:


Rich (BB code): 
If a(i, 1) = "BUYING" Or a(i, 1) = "SELLING RETURNS" Then Qty = Qty + a(i, 6) Else Qty = Qty - a(i, 6)


😇
Click to expand...
I'm shocked !!!!:eek::eek::eek:
I thought all of lines I check them , sorry !🙏
every thing is really great .
many thanks for your help.👍
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
compare brands between two sheets and show minus and positive values based on date (today)
Replies
6
Views
236
abdo meghari
A
A
  • Solved
merge QTY on textbox based on selected combobox on userform if the adjacent cell doesn't equal zero
Replies
2
Views
300
abdo meghari
A
A
  • Solved
put the QTY to adjacent ID based on arranged sheet when compare two sheets
Replies
7
Views
262
abdo meghari
A
A
  • Solved
Replace formula with zero when populate Right mark for two sheets
Replies
3
Views
367
awoohaw
awoohaw
A
  • Solved
add / subtract new price in cell to multiple cells in column based on two cells values
Replies
7
Views
156
abdo meghari
A

Forum statistics

Threads
1,223,881
Messages
6,175,159
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top