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

 

[Akuini]

before insert row I want fill blank row before TOTAL row if I have already blank row

Sorry for the late reply.
I don't understand what you meant. Can you explain in more detail?

 

[Ali M]

thanks for come back for me .
take this picture
there is empty row for range

insert row.xlsm
	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
9		TOTAL	224,500.00	132,500.00	92,000.00
ورقة1

Cell Formulas
Range	Formula
L4,L9	L4	=J4-K4
L5:L7	L5	=L4+J5-K5
J9	J9	=SUM(J4:J7)
K9	K9	=SUM(K4:K8)

and when fill by userform
shouldn't insert new row before fill data in empty row if there is empty row

another process

when there is no empty row , then the original code does it
before

insert row.xlsm
	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	20/07/2023	EXPENSE PR		1,200.00	90,800.00
9		TOTAL	224,500.00	133,700.00	90,800.00
ورقة1

Cell Formulas
Range	Formula
L4,L9	L4	=J4-K4
L5:L8	L5	=L4+J5-K5
J9	J9	=SUM(J4:J7)
K9	K9	=SUM(K4:K8)

after it

 

[Akuini]

take this picture
there is empty row for range

Could there be more than 1 empty rows above TOTAL?

 

[Akuini]

If it's only 1 row then try this:

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
                    
        If WorksheetFunction.CountA(.Range("H" & i - 1).Resize(, 5)) = 0 Then
            i = i - 1
            .Range("H" & i).Resize(, 5).Delete Shift:=xlUp
        End If
        
        .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]

sorry for delaying to answer your question !

Could there be more than 1 empty rows above TOTAL?

yes

 

[Akuini]

sorry for delaying to answer your question !

yes

So, if there are multiple empty rows, what should be the result? Should we delete all empty rows except one, which will be filled with new data? Or should we just fill the first empty row with new data?

 

[Ali M]

Or should we just fill the first empty row with new data?

yes should be .

 

[Akuini]

yes should be .

Try this one:

Private Sub CommandButton1_Click()
Dim i As Long, q As Long, n As Long, h 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
                    If h = 0 And va(i, 1) = "" Then
                        h = i
                    End If
                    i = i + 1
                Loop Until va(i, 1) = "TOTAL"
            Exit For
            End If
        Next
             
                   
        If h > 0 Then
            xx = h
            i = i - 1
        Else
            xx = i
           .Range("H" & i).Resize(, 5).Insert Shift:=xlDown  'add new row in col H:L
        End If
        
        
        'in new row
        .Range("H" & xx) = Date  'insert today
        .Range("I" & xx) = TextBox1
        If Len(Trim(TextBox2)) > 0 Then .Range("J" & xx) = Val(TextBox2)
        If Len(Trim(TextBox3)) > 0 Then .Range("K" & xx) = Val(TextBox3)
        .Range("L" & xx) = Evaluate("=(L" & xx - 1 & "+ J" & xx & "- K" & xx & ")")
        
        '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("J" & i + 1) - .Range("K" & i + 1)  'balance
     
        'format new row
        .Range("H" & xx - 1).Resize(, 5).Copy
        .Range("H" & xx).PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
    End If


End With

Exit Sub

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

End Sub

 

[Ali M]

magnificent !
I' m grateful for your time and assistance !
thanks again buddy.

 

[Akuini]

You're welcome.