abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 718
- Office Version
- 2019
Hi,
I will add TB6,TB7 when writing dates to search data within showed in listbox ,
also when run the form should merge data on form for PURCHASE ,SALES sheets.
brings just BRAND from PURCHASE sheet based on BRANDS are existed in SALES sheet , ignore new brands in PURCHASE sheet.
so should be as the picture
should merge QTY based on BRAND column for SALES sheet, as SALES header will sum TOTAL column and divide on QTY sum
ex: BS 1200R20 G580 JAP =81600/40=2040
as PURCHASE header =100500/50=2010
as formula in TOTAL column=(2040-2010)*40
add sum word in first column and sum columns 3:5 as show in listbox.
I look forward if there is way to bold fonts for headers and SUM row in last row in listbox
the data in sheets.
without effect the others procedures
based on the others procedures will select option button based on sheet name then will show data for sheet name and search in textbox2 for brand to merge QTY and calculate price in others textboxs , so should keep theses procedures without any change when add new procedure as in above procedure
here is the procedures
I hope to getting chance to complete this project.
I will add TB6,TB7 when writing dates to search data within showed in listbox ,
also when run the form should merge data on form for PURCHASE ,SALES sheets.
brings just BRAND from PURCHASE sheet based on BRANDS are existed in SALES sheet , ignore new brands in PURCHASE sheet.
so should be as the picture
should merge QTY based on BRAND column for SALES sheet, as SALES header will sum TOTAL column and divide on QTY sum
ex: BS 1200R20 G580 JAP =81600/40=2040
as PURCHASE header =100500/50=2010
as formula in TOTAL column=(2040-2010)*40
add sum word in first column and sum columns 3:5 as show in listbox.
I look forward if there is way to bold fonts for headers and SUM row in last row in listbox
the data in sheets.
populating data on form.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | CUTOMER | ID | BRAND | QTY | UNIT PRICE | TOTAL | ||
2 | 01/01/2023 | CRR-100 | BSJ100 | BS 1200R20 G580 JAP | 20.00 | 2,000.00 | 40,000.00 | ||
3 | 02/01/2023 | CRR-101 | BSJ101 | BS 750R16 R230 JAP | 10.00 | 715.00 | 7,150.00 | ||
4 | 03/01/2023 | CRR-102 | BSJ102 | BS 750R16 VSJ JAP | 20.00 | 730.00 | 14,600.00 | ||
5 | 04/01/2023 | CRR-101 | BSJ100 | BS 1200R20 G580 JAP | 20.00 | 2,035.00 | 40,700.00 | ||
6 | 05/01/2023 | CRR-101 | BSJ102 | BS 750R16 VSJ JAP | 10.00 | 710.00 | 7,100.00 | ||
7 | 06/01/2023 | CRR-100 | BSJ100 | BS 1200R20 G580 JAP | 10.00 | 1,980.00 | 19,800.00 | ||
8 | 07/01/2023 | CRR-103 | BSJ103 | BS 1200R20 G580 THI | 50.00 | 2,025.00 | 101,250.00 | ||
9 | 08/01/2023 | CRR-104 | BSJ104 | BS 1200R20 R187 JAP | 10.00 | 1,980.00 | 19,800.00 | ||
10 | 09/01/2023 | CRR-105 | BSJ105 | BS 205/70R15C R623 | 50.00 | 400.00 | 20,000.00 | ||
11 | 10/01/2023 | CRR-105 | BSJ103 | BS 1200R20 G580 THI | 40.00 | 2,000.00 | 80,000.00 | ||
12 | 11/01/2023 | CRR-104 | BSJ105 | BS 205/70R15C R623 | 15.00 | 430.00 | 6,450.00 | ||
13 | 12/01/2023 | CRR-101 | BSJ105 | BS 205/70R15C R623 | 20.00 | 440.00 | 8,800.00 | ||
PURCHASE |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G13 | G2 | =E2*F2 |
populating data on form.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | CUTOMER | ID | BRAND | QTY | UNIT PRICE | TOTAL | ||
2 | 01/02/2023 | SRR-100 | BSJ100 | BS 1200R20 G580 JAP | 20.00 | 2,000.00 | 40,000.00 | ||
3 | 02/02/2023 | SRR-100 | BSJ105 | BS 205/70R15C R623 | 5.00 | 460.00 | 2,300.00 | ||
4 | 03/02/2023 | SRR-101 | BSJ105 | BS 205/70R15C R623 | 5.00 | 465.00 | 2,325.00 | ||
5 | 04/02/2023 | SRR-101 | BSJ101 | BS 750R16 R230 JAP | 2.00 | 740.00 | 1,480.00 | ||
6 | 05/02/2023 | SRR-102 | BSJ101 | BS 750R16 R230 JAP | 2.00 | 755.00 | 1,510.00 | ||
7 | 05/02/2023 | SRR-103 | BSJ101 | BS 750R16 R230 JAP | 2.00 | 760.00 | 1,520.00 | ||
8 | 06/02/2023 | SRR-100 | BSJ100 | BS 1200R20 G580 JAP | 20.00 | 2,080.00 | 41,600.00 | ||
SALES |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G8 | G2 | =E2*F2 |
without effect the others procedures
based on the others procedures will select option button based on sheet name then will show data for sheet name and search in textbox2 for brand to merge QTY and calculate price in others textboxs , so should keep theses procedures without any change when add new procedure as in above procedure
here is the procedures
VBA Code:
Option Explicit
Dim a As Variant
Sub FilterData()
Dim txt1 As String
Dim Tot4 As Double, Tot5 As Double, Tot6 As Double '++
Dim i As Long, j As Long, k As Long
ListBox1.Clear
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
For i = 1 To UBound(a)
If TextBox2 = "" Then txt1 = a(i, 4) Else txt1 = TextBox2
If LCase(a(i, 4)) Like LCase(txt1) & "*" Then
k = k + 1
For j = 1 To 7
If j = 1 Then 'Change the 1 in this line for the column number where you have the date
b(k, j) = Format(a(i, j), "dd/mm/yyyy")
Else
b(k, j) = a(i, j)
If k = 1 And j = 3 Then Me.TextBox1.Value = a(i, j) '+++
End If
Next
End If
Next
If k > 0 Then ListBox1.List = b
With ListBox1
.ColumnWidths = "80;80;80;180;100;80;80"
For i = 0 To .ListCount - 1
Tot4 = Tot4 + .List(i, 4) '++
.List(i, 4) = Format(.List(i, 4), "#,##0.00")
Tot5 = Tot5 + .List(i, 5) '++
.List(i, 5) = Format(.List(i, 5), "#,##0.00")
Tot6 = Tot6 + .List(i, 6) '++
.List(i, 6) = Format(.List(i, 6), "#,##0.00")
Next i
End With
Me.TextBox3.Value = Format(Tot4, "#,##0.00")
If Tot4 > 0 Then
Me.TextBox4.Value = Format(Tot6 / Tot4, "#,##0.00")
Else
Me.TextBox4.Text = ""
End If
Me.TextBox5.Value = Tot6
End Sub
Private Sub OptionButton1_Click()
If OptionButton1.Value = True And OptionButton2.Value = False Then sheet5.Select
Call ChangeSheet
Call FilterData
End Sub
Private Sub OptionButton2_Click()
If OptionButton2.Value = True And OptionButton1.Value = False Then sheet6.Select
Call ChangeSheet
Call FilterData
End Sub
Private Sub TextBox2_Change()
Call FilterData
End Sub
Private Sub ChangeSheet()
With ActiveSheet
a = .Range("A2:G" & .Range("D" & Rows.Count).End(3).Row).Value
End With
End Sub
I hope to getting chance to complete this project.