I am new to macros and please help me solve the below issue. I have the below macro for the macro enabled workbook, in this I am returning the sum of values being entered to the column from F15 to cell H9 but its not getting updated. the same i have done for the column B for count of the values and its getting updated. Please check and let me know and I could provide any additional details required.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Not Application.CutCopyMode And IsSelectionNotEmptyOrNumeric(Target) Then ' Check if cell contains a numeric value or is not empty and the change was made by the user
If Target.Cells.Count > 1 Then ' Check if multiple cells are selected
Dim deleteRows As Range ' Create a range object to store rows to delete
For Each cell In Target.Cells
If cell.Value <> "" Then
Range("A" & cell.Row).Formula = "=IF(B" & cell.Row & "<>"""",ROW()-ROW($A$15)+1,"""")"
Range("D" & cell.Row).Formula = "=INDEX(PriceList!$C$2:$C$7956,MATCH(B" & cell.Row & ",PriceList!$A$2:$A$7956,0))"
Range("H" & cell.Row).Formula = "=IF(B" & cell.Row & "<>"""",G" & cell.Row & "*F" & cell.Row & ","""")"
' Set horizontal alignment
With Range("A" & Target.Row)
.HorizontalAlignment = xlCenter
End With
With Range("B" & Target.Row & ":D" & Target.Row)
.HorizontalAlignment = xlLeft
End With
With Range("E" & Target.Row & ":F" & Target.Row)
.HorizontalAlignment = xlCenter
.NumberFormat = "0"
End With
With Range("G:H")
.NumberFormat = "0.00_-;[Red]-0.00_-;""-""??_-;@"
.HorizontalAlignment = xlRight
End With
' Copy border, border color and orientation from row above
With Range("A" & cell.Row & ":H" & cell.Row)
.Borders.LineStyle = .Offset(-1, 0).Borders.LineStyle
.Borders.Color = .Offset(-1, 0).Borders.Color
.Orientation = .Offset(-1, 0).Orientation
.HorizontalAlignment = .Offset(-1, 0).HorizontalAlignment
End With
Else
' Add row to deleteRows range
If deleteRows Is Nothing Then
Set deleteRows = Rows(cell.Row)
Else
Set deleteRows = Union(deleteRows, Rows(cell.Row))
End If
End If
Next cell
With Range("H8:H10")
.HorizontalAlignment = xlCenter
.NumberFormat = "0"
End With
Range("H8").Value = Application.WorksheetFunction.CountA(Range("B15:B" & Cells(Rows.Count, "B").End(xlUp).Row))
Range("H9").Value = Application.WorksheetFunction.Sum(Range("E15:E" & Cells(Rows.Count, "E").End(xlUp).Row)) ' Update sum in H9
Range("H10").Value = Application.WorksheetFunction.Sum(Range("F15:F" & Cells(Rows.Count, "F").End(xlUp).Row))
Range("H11").Value = Application.WorksheetFunction.Sum(Range("H15:H" & Cells(Rows.Count, "H").End(xlUp).Row))
' Delete entire rows in deleteRows range
If Not deleteRows Is Nothing Then
Application.EnableEvents = False ' Disable events to avoid triggering the event again
deleteRows.Delete
Application.EnableEvents = True ' Enable events again
End If
Else
If Target.Value <> "" Then
Range("A" & Target.Row).Formula = "=IF(B" & Target.Row & "<>"""",ROW()-ROW($A$15)+1,"""")"
Range("D" & Target.Row).Formula = "=INDEX(PriceList!$C$2:$C$7956,MATCH(B" & Target.Row & ",PriceList!$A$2:$A$7956,0))"
Range("H" & Target.Row).Formula = "=IF(B" & Target.Row & "<>"""",G" & Target.Row & "*F" & Target.Row & ","""")"
' Set horizontal alignment
With Range("A" & Target.Row)
.HorizontalAlignment = xlCenter
End With
With Range("B" & Target.Row & ":D" & Target.Row)
.HorizontalAlignment = xlLeft
End With
With Range("E" & Target.Row & ":F" & Target.Row)
.HorizontalAlignment = xlCenter
.NumberFormat = "0"
End With
With Range("G:H")
.NumberFormat = "0.00_-;[Red]-0.00_-;""-""??_-;@"
.HorizontalAlignment = xlRight
End With
' Copy border, border color and orientation from row above
With Range("A" & Target.Row & ":H" & Target.Row)
.Borders.LineStyle = .Offset(-1, 0).Borders.LineStyle
.Borders.Color = .Offset(-1, 0).Borders.Color
.Orientation = .Offset(-1, 0).Orientation
.HorizontalAlignment = .Offset(-1, 0).HorizontalAlignment
End With
With Range("H8:H10")
.HorizontalAlignment = xlCenter
.NumberFormat = "0"
End With
Range("H8").Value = Application.WorksheetFunction.CountA(Range("B15:B" & Cells(Rows.Count, "B").End(xlUp).Row))
Range("H9").Value = Application.WorksheetFunction.Sum(Range("E15:E" & Cells(Rows.Count, "E").End(xlUp).Row)) ' Update sum in H9
Range("H10").Value = Application.WorksheetFunction.Sum(Range("F15:F" & Cells(Rows.Count, "F").End(xlUp).Row))
Range("H11").Value = Application.WorksheetFunction.Sum(Range("H15:H" & Cells(Rows.Count, "H").End(xlUp).Row))
Else
' Delete entire row
Application.EnableEvents = False ' Disable events to avoid triggering the event again
Rows(Target.Row).Delete
Application.EnableEvents = True ' Enable events again
End If
End If
End If
End If
End Sub
Private Function IsSelectionNotEmptyOrNumeric(selection As Range) As Boolean
IsSelectionNotEmptyOrNumeric = False
For Each cell In selection.Cells
If IsNumeric(cell.Value) Or cell.Value <> "" Then
IsSelectionNotEmptyOrNumeric = True
Exit For
End If
Next cell
End Function
The lines which are not executing are the below
Range("H9").Value = Application.WorksheetFunction.Sum(Range("E15:E" & Cells(Rows.Count, "E").End(xlUp).Row)) ' Update sum in H9
Range("H10").Value = Application.WorksheetFunction.Sum(Range("F15:F" & Cells(Rows.Count, "F").End(xlUp).Row))
Range("H11").Value = Application.WorksheetFunction.Sum(Range("H15:H" & Cells(Rows.Count, "H").End(xlUp).Row))
