insert row after last duplicated item based on selected combobox on userform

[Ali M]

Hello
I want copying data from userform to sheet("daily") by insert new row after last row contains duplicate item in column b and match with selected combobox ,
then should copy combobox1,textbox1,textbox2= columns B,C,D ,as to column A should populate date(today).

the result as I highlighted

ase.xlsm
	A	B	C	D
1	DATE	ACCOUNT NAME	DEBIT	CREDIT
2	01/03/2023	CASH BALANCE	200,000.00
3	03/03/2023	CASH PR		2,000.00
4	03/03/2023	CASH PR		15,000.00
5	09/03/2023	CASH PR		500.00
6	13/03/2023	CASH PR		10,000.00
7	13/03/2023	CASH PR	15,000.00
8	14/03/2023	CASH PR		500.00
9	19/03/2023	CASH PR		500.00
10	19/03/2023	CASH PR		2,000.00
11	17/06/2023	CASH PR	12,000.00
12	05/03/2023	CASH DM	2,000.00
13	10/03/2023	CASH DM	500.00
14	15/03/2023	CASH DM	5,000.00
15	15/03/2023	CASH DM	2,000.00
16	15/03/2023	CASH DM		10,000.00
17	18/03/2023	CASH DM		25,000.00
18	19/03/2023	CASH DM		2,000.00
19	21/03/2023	EXPENSE PR	15,000.00
20	21/03/2023	EXPENSE PR	20,000.00
21	23/03/2023	EXPENSE PR	2,000.00
22	24/03/2023	SALES		200000
23	26/03/2023	SALES		1000
24	25/03/2023	PURCHASE	10000
DAILY

 

[DanteAmor]

Don't mention what to do if the account name doesn't exist.

With the following macro it sends you a msg that the account does not exist.
If you want to add the record, it will add it to the end of the data, just remove this line: "Exit Sub" from the macro.

Note: Check your data, some account names have spaces to the right. If you don't correct it, the macro won't find them.

Private Sub CommandButton1_Click()
  Dim f As Range
  Dim sh As Worksheet
  Dim lr As Long
  
  Set sh = Sheets("DAILY")
  
  If ComboBox1.ListIndex = -1 Or (TextBox1.Value = "" And TextBox2.Value = "") Then
    MsgBox "You must complete the data."
    Exit Sub
  End If
  
  Set f = sh.Range("B:B").Find(ComboBox1.Value, , xlValues, xlWhole, xlByRows, xlPrevious, False)
  If f Is Nothing Then
    MsgBox "ACCOUNT NAME not exists"
    lr = sh.Range("B" & Rows.Count).End(3).Row + 1
    Exit Sub
  Else
    lr = f.Row + 1
    sh.Rows(lr).EntireRow.Insert
  End If
  
  sh.Range("A2:D2").Copy
  sh.Range("A" & lr).PasteSpecial xlPasteFormats
  sh.Range("C2").Copy
  sh.Range("D" & lr).PasteSpecial xlPasteFormats
  Application.CutCopyMode = False
  sh.Range("D" & lr).Font.Color = vbBlack
  sh.Range("A" & lr).Value = Date
  sh.Range("B" & lr).Value = ComboBox1.Value
  sh.Range("C" & lr).Value = TextBox1.Value
  sh.Range("D" & lr).Value = TextBox2.Value
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Ali M]

Don't mention what to do if the account name doesn't exist.

well the items in combobox depends on what is existed in column B , so just I select not write in combobox. but thanks for this notice .

Note: Check your data, some account names have spaces to the right. If you don't correct it, the macro won't find them.

understood
thank you so much for your code .