complicated to insert row by userform based on matching combobox before TOTAL row

[Ali M]

Hi experts ,
I search for way to insert new row before TOTAL row for each separated range based on match name for column I .
if the selected name from combobox1 is matched with name in column I for separated range , then should insert new row before TOTAL row and recalculation based on previous row and change sum TOTAL values for columns J,K,l ,but in reality I don't have any formulas whether in Total row or column L , just to understand who calculate before insert row and how cold be after insert row .
so when fill data on userform for textbox1=column I, textbox2= column J, textbox3=column K , as to column H should populate date(today)

so this is when I fill userform

and see the highlighted as in result

insert row (version 1).xlsb
	H	I	J	K	L
1		NAME
2		ALI
3	DATE	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
4	20/06/2023	CASH BALANCE	200,000.00		200,000.00
5	21/06/2023	CASH PR	15,000.00	30,500.00	184,500.00
6	22/06/2023	CASH DM	9,500.00	37,000.00	157,000.00
7	22/06/2023	EXPENSE ADMIN		65,000.00	92,000.00
8	23/06/2023	EXPENSE PR		1,200.00	90,800.00
9		TOTAL	224,500.00	133,700.00	90,800.00
10
11		NAME
12		OMAR
13	ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
14	20/06/2023	PURCHASE	15,000.00		15,000.00
15	21/06/2023	STOCK1	100,000.00		115,000.00
16	22/06/2023	PURCHASE RETURNS		4,000.00	111,000.00
17	22/06/2023	PURCHASE LOW	6,200.00		117,200.00
18	22/06/2023	EXPENSE PR	37,000.00		154,200.00
19		TOTAL	158,200.00	4,000.00	154,200.00
20
21		NAME
22		MAHMOUD
23	ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
24	22/06/2023	SALES		201,000.00	-201,000.00
25	22/06/2023	SALES RETURNS	3,500.00		-197,500.00
SE

Cell Formulas
Range	Formula
L4,L24,L19,L14,L9	L4	=J4-K4
L25,L15:L18,L5:L8	L5	=L4+J5-K5
J9	J9	=SUM(J4:J7)
K9,J19:K19	K9	=SUM(K4:K8)

thanks in advance

 

[Ali M]

could achieve this , experts?

 

[Ali M]

Bump

 

[Akuini]

but in reality I don't have any formulas whether in Total row or column L ,

I'm confused, you don't have formula in Total row? so where did the numbers come from?

just to understand who calculate before insert row and how cold be after insert row

what do you mean by this?

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

 

[Ali M]

Hi Akuini ,

I'm confused, you don't have formula in Total row? so where did the numbers come from?

yes , the number come from another macro . you can run it and see it what happens.

what do you mean by this?

when I say there is no formula you need to know how calculate the inserted row so I put the formula manually to know from the basic how calculate it. so the calculation depends on previous row in column L as I did it .
here is the file
insert row.xlsm

 

[Akuini]

I've downloaded your file. I'll try to write the code tomorrow.

 

[Akuini]

I changed the sheet name to "Daily".
Try this code for commandbutton:

Private Sub CommandButton1_Click()
Dim i As Long, q As Long, n As Long
Dim va

On Error GoTo Skip:

With Sheets("Daily")
    
    va = .Range("i1", .Cells(.Rows.Count, "i").End(xlUp))

    If ComboBox1.ListIndex > -1 Then
    
        'find total row below the name selected in combobox1
        For i = 1 To UBound(va, 1)
            If LCase(va(i, 1)) = LCase(ComboBox1.Text) Then
                q = i
                Do
                    i = i + 1
                Loop Until va(i, 1) = "TOTAL"
            Exit For
            End If
        Next
        
        .Range("H" & i).Resize(, 5).Insert Shift:=xlDown  'add new row in col H:L
        
        'in new row
        .Range("H" & i) = Date  'insert today
        .Range("I" & i) = TextBox1
        If Len(Trim(TextBox2)) > 0 Then .Range("J" & i) = Val(TextBox2)
        If Len(Trim(TextBox3)) > 0 Then .Range("K" & i) = Val(TextBox3)
        .Range("L" & i) = Evaluate("=(L" & i - 1 & "+ J" & i & "- K" & i & ")")
        
        'in total rows
        .Range("J" & i + 1) = Evaluate("=SUM(J" & q & ":J" & i & ")")
        .Range("K" & i + 1) = Evaluate("=SUM(K" & q & ":K" & i & ")")
        '.Range("L" & i + 1) = Evaluate("=(J" & i + 1 & "- K" & i + 1 & ")")
        .Range("L" & i + 1) = .Range("L" & i)  'balance
     
        'format new row
        .Range("H" & i - 1).Resize(, 5).Copy
        .Range("H" & i).PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
    End If


End With

Exit Sub

Skip:
MsgBox "Error number " & Err.Number & " : " & Err.Description

End Sub

 

[Ali M]

I 'm grateful for you dedication your time to help me .
thank you so much .

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[Ali M]

Hi Akuini
I need help for this part of my problem !
before insert row I want fill blank row before TOTAL row if I have already blank row . and if no blank row ,then the original code should insert new row as you did it.
thanks again