VBA for adding formulas to specific Columns when a new row is added.

[Jstump]

Hello,

I have formulas set up in coulmn 9-15 (I-O). On this sheet "hours" a row is either added or deleted depending on if data is entered or deleted off of another selection on another sheet "Input sheet". Im trying to make it where a new row is added the formulas are already there so i do not have to manually imput them every time. The only cells where data is entered are in yellow. Here are the 2 sheets i am working off of for reference. Ive also attached the code I have so far just for the last column.

Any help would be amazing!!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim newRow As Long

    Set ws = ThisWorkbook.Sheets("Hours")

  
    If Target.Column = 1 Then
        
        If Target.Rows.Count > 1 Then
            Application.EnableEvents = False

            
            newRow = 6

            
            ws.Cells(newRow, 15).FormulaArray = "=FILTER(Database!$C$2:$C$10, Database!$A$2:$A$10=A" & newRow & ", """")"

            
            Application.EnableEvents = True
        End If
    End If
End Sub

Kane Macro copy 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4		14-Jan	15-Jan	16-Jan	17-Jan	18-Jan	19-Jan	20-Jan	Straight Total	OT Total	Straight $	OT $	Weekly Total	Total
5
6	Stump, James	10	10	10	10	10	10	1	40	21	1461.54	1151	$2,612.50	61.0	36.538
7	Total Straight Hrs	#REF!
8	Total OT HRs	#REF!
9	Total Weekly HRS	#REF!
10	Total weekly cost	#REF!
Hours

Cell Formulas
Range	Formula
I6	I6	=IF(N6>40,40,N6)
J6	J6	=IF(N6>40,N6-40,0)
K6	K6	=I6*O6
L6	L6	=(O6*1.5)*J6
M6	M6	=SUM(K6:L6)
N6	N6	=SUM(B6:H6)
O6	O6	=FILTER(Database!$C$2:$C$10,Database!$A$2:$A$10=A6,"")
B7	B7	=N61
B8	B8	=O61
B9	B9	=S61
B10	B10	=R61*1.22

Kane Macro copy 1.xlsm
	O	P
3	Employee	Employee name
4	1	Stump, James
5	2
6	3
7	4
8	5
9	6
10	7
11	8
12	9
13	10
14	11
15	12
16	13
17	14
18	15
19	16
20	17
21	18
22	19
23	20
Input page

Cells with Data Validation
Cell	Allow	Criteria
P4:P23	List	=Database!$A$2:$A$10

 

[Jstump]

This is what i have now and it is not working. I'm Lost. the first one is for when a new row is added in just row 6 and the second one iswhen a row is added in gerenal.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Hours")

    If Target.Column = 1 And Target.Rows.Count > 1 Then
        Application.EnableEvents = False
        ws.Cells(6, 9).Formula = "=IF(N6>40,40,N6)"
        ws.Cells(6, 10).Formula = "=IF(N6>40,N6-40,0)"
        ws.Cells(6, 11).Formula = "=I6*O6"
        ws.Cells(6, 12).Formula = "=(O6*1.5)*J6"
        ws.Cells(6, 13).Formula = "=SUM(K6:L6)"
        ws.Cells(6, 14).Formula = "=SUM(B6:H6)"
        ws.Cells(6, 15).FormulaArray = "=FILTER(Database!$C$2:$C$10,Database!$A$2:$A$10=A6,"""")"
        Application.EnableEvents = True
    End If
End Sub

[/CODE]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim newRow As Long
    Dim formulaRange As Range

    Set ws = ThisWorkbook.Sheets("Hours")

    If Target.Column = 1 Then
        If Target.Rows.Count > 1 Then
            Application.EnableEvents = False
            newRow = Target.Cells(1, 1).Row

            Set formulaRange = ws.Range(ws.Cells(newRow, 9), ws.Cells(newRow, 15))

            formulaRange.Formula = Array( _
                "=IF(N" & newRow & ">40,40,N" & newRow & ")", _
                "=IF(N" & newRow & ">40,N" & newRow & "-40,0)", _
                "=I" & newRow & "*O" & newRow, _
                "=(O" & newRow & "*1.5)*J" & newRow, _
                "=SUM(K" & newRow & ":L" & newRow & ")", _
                "=SUM(B" & newRow & ":H" & newRow & ")", _
                "=FILTER(Database!$C$2:$C$10,Database!$A$2:$A$10=A" & newRow & ","""")" _
                )

            Application.EnableEvents = True
        End If
    End If
End Sub

 

[Jstump]

CAn anyone help witht this?? it is still not working.

 

[dreid1011]

CAn anyone help witht this?? it is still not working.

Upon testing your code, it does indeed do nothing as far as one can tell.

When exactly is the code supposed to run? And what row should be populated with the formulas once it has run?

 

[Jstump]

It is suppose to run automatically when a row is added to the "Hours" sheet and the formulas are populated into the new row in columns 9-15 (I-O).

 

[dreid1011]

It is suppose to run automatically when a row is added to the "Hours" sheet and the formulas are populated into the new row in columns 9-15 (I-O).

Okay, it does seem to be working, but only if you have more than one cell selected when you make any changes. Inserting a row does not satisfy your condition of "Target.Rows.Count > 1" unless you select multiple cells in column A and right click the sheet to insert. If you right click the left side of the sheet to insert an entire row, the code does not do what you want. If you change the condition to count Columns instead of Rows, I think it might work as intended.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim newRow As Long
    Dim formulaRange As Range

    Set ws = ThisWorkbook.Sheets("Hours")

    If Target.Column = 1 Then
        If Target.Columns.Count > 1 Then
            Application.EnableEvents = False
            newRow = Target.Cells(1, 1).Row

            Set formulaRange = ws.Range(ws.Cells(newRow, 9), ws.Cells(newRow, 15))

            formulaRange.Formula = Array( _
                "=IF(N" & newRow & ">40,40,N" & newRow & ")", _
                "=IF(N" & newRow & ">40,N" & newRow & "-40,0)", _
                "=I" & newRow & "*O" & newRow, _
                "=(O" & newRow & "*1.5)*J" & newRow, _
                "=SUM(K" & newRow & ":L" & newRow & ")", _
                "=SUM(B" & newRow & ":H" & newRow & ")", _
                "=FILTER(Database!$C$2:$C$10,Database!$A$2:$A$10=A" & newRow & ","""")" _
                )

            Application.EnableEvents = True
        End If
    End If
End Sub

 

[Jstump]

This worked. Thanks!

 

[dreid1011]

This worked. Thanks!

You're welcome, and happy to help.