Conflicting Codes HELP!

J

Jstump

New Member
Joined
Oct 25, 2023
Messages
36
Office Version
  1. 365
Platform
  1. Windows
I have a formula set up to where when information is added into column 19 on INput page it adds the information into line 36 on Hours. i set certain data to stay in line 35 and tried to get it to delete the data when a line is removed or added and keep the data in line 35. However, that part of the code is interefering with the data being autopopulated into lin 36. I have tried to set it to autopoluating into line 37 or 38 and it still did not help. the first code is the original where it auto added information and the secdon code is what i have now adding (keep the data in line 35 and auto delete when a row is added or deleted. I cant figure out how to make them work together. Ive added mini charts so if you need to work within the workbook to help.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge <> 1 Then Exit Sub

    Application.ScreenUpdating = False

    If Target.Column = 16 Then
        With Sheets("Hours")
            If Target.Value = "" Then
                .Rows(6).Delete
            Else
                .Rows(6).Insert
                .Range("A6") = Target.Value
            End If
        End With
    ElseIf Target.Column = 19 Then
        With Sheets("Hours")
            If Target.Value = "" Then
                .Rows(36).Delete
            Else
                .Rows(36).Insert
                .Range("A36") = Target.Value
            End If
        End With
    ElseIf Target.Column = 22 Then
        With Sheets("Hours")
            If Target.Value = "" Then
                .Rows(64).Delete
            Else
                .Rows(64).Insert
                .Range("A64") = Target.Value
            End If
        End With
    ElseIf Target.Column = 25 Then
        With Sheets("Hours")
            If Target.Value = "" Then
                .Rows(93).Delete
            Else
                .Rows(93).Insert
                .Range("A93") = Target.Value
            End If
        End With
    End If
    
    Application.ScreenUpdating = True
End Sub

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge <> 1 Then Exit Sub

    Application.ScreenUpdating = False

    If Target.Column = 16 Then
        With Sheets("Hours")
            If Target.Value = "" Then
                .Rows(6).Delete
            Else
                .Rows(6).Insert
                .Range("A6") = Target.Value
            End If
        End With
    ElseIf Target.Column = 19 Then
        With Sheets("Hours")
            If Target.Value = "" Then
                .Rows(36).Delete
            Else
                .Rows(36).Insert
                .Range("A36") = Target.Value
            End If
        End With
    ElseIf Target.Column = 22 Then
        With Sheets("Hours")
            If Target.Value = "" Then
                .Rows(64).Delete
            Else
                .Rows(64).Insert
                .Range("A64") = Target.Value
            End If
        End With
    ElseIf Target.Column = 25 Then
        With Sheets("Hours")
            If Target.Value = "" Then
                .Rows(93).Delete
            Else
                .Rows(93).Insert
                .Range("A93") = Target.Value
            End If
        End With
    End If

    ' Ensure data stays in line 35
    With Sheets("Hours")
        If .Rows(35).EntireRow.Hidden = False Then
            .Rows(36).EntireRow.Delete
            .Range("I35:N35").ClearContents
            .Range("I35").Value = "Straight Total"
            .Range("J35").Value = "OT Total"
            .Range("K35").Value = "Straight $"
            .Range("L35").Value = "OT $"
            .Range("M35").Value = "Weekly Total"
            .Range("N35").Value = "Total"
        End If
    End With

    Application.ScreenUpdating = True
End Sub

Kane Macro copy 2.xlsm
OPRSUVXY
2Week 1Week 2Week 3Week 4
3Employee Employee NameEmployee nameEmployee nameEmployee name
41Stump, JamesClement, Vincent
52Stump, JamesClement, Vincent
63McCain, Austin
74
85
96
107
118
129
1310
1411
1512
1613
1714
1815
1916
2017
2118
2219
2320
Project Input page
Cells with Data Validation
CellAllowCriteria
Y4:Y23List=Database!$A$2:$A$10
S4:S21List=Database!$A$2:$A$10
V4:V23List=Database!$A$2:$A$10
P4:P21List=Database!$A$2:$A$10


Kane Macro copy 2.xlsm
ABCDEFGHIJKLMNPQRST
1
2
3
414-Jan15-Jan16-Jan17-Jan18-Jan19-Jan20-JanStraight TotalOT TotalStraight $OT $Weekly TotalTotalStraight $OT $Weekly Total
5
6Stump, James000000
7Daggy, Dillain000000
8Clement, Vincent000000
9Clement, Vincent000000
10
11Totals0000000000000
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35Straight TotalOT TotalStraight $OT $Weekly TotalTotal
3600#VALUE!#VALUE!#VALUE!0
37
38
39
40
41
42
43
44
45
46
47
48
49
Hours
Cell Formulas
RangeFormula
I6:I9,I36I6=IF(N6>40,40,N6)
J6:J9,J36J6=IF(N6>40,N6-40,0)
K6:K9,K36K6=I6*O6
L6:L9,L36L6=(O6*1.5)*J6
M6:M9,M36M6=SUM(K6:L6)
N6:N9,N36,N11N6=SUM(B6:H6)
B11:M11B11=SUM(B5:B10)
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Try below code ...
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge <> 1 Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

With Sheets("Hours")
   Select Case Target.Column
      Case 16: If Target.Value = "" Then .Rows(6).Delete Else .Rows(6).Insert: .[A6] = Target.Value
      Case 19: If Target.Value = "" Then .Rows(36).Delete Else .Rows(36).Insert: .[A36] = Target.Value
      Case 22: If Target.Value = "" Then .Rows(64).Delete Else .Rows(64).Insert: .[A64] = Target.Value
      Case 25: If Target.Value = "" Then .Rows(93).Delete Else .Rows(93).Insert: .[A93] = Target.Value
   End Select
   If .Rows(35).EntireRow.Hidden = False Then
      .Rows(36).EntireRow.Delete
      .[I35:N35] = [{"Straight Total","Straight Total","OT Total","Straight $","OT $","Weekly Total","Total"}]
   End If
End With

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
 
Upvote 0
that worked but now it conflicts with this that i had in my "hours" sheet.
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.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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA code is not working
2
Replies
11
Views
444
smozgur
smozgur
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
262
tixarah
T
J
  • Solved
Combining VBA Codes
Replies
7
Views
434
Joe4
Joe4
J
  • Solved
Run time error for Worksheet Change in VBA
2
Replies
17
Views
395
jtemp57
J
A
  • Question Question
After User Enters A Value (Validation List), Not Automatically Advancing To Next Cell As Per "Select"
Replies
1
Views
382
Ark68
A

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top