vipulhumein
09-13-2013, 03:07 AM
hello
i have made a sheet with automatic row insertion if column f is less than column h. now i want that whenever the row is inserted than a formula should be entered in the row which is inserted below with formula (column f - column h) of the above row .
Example:-say if i have entered in row 4 of column f value 10 and row 4 of column h value 9 than a row is automatically inserted just below row 4 , now i want that in new row which is inserted just below column 4 say Row 5 is inserted , in that row 5 column f i want a formula that is f4-h4 so that the balance value may come
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const SALES_PWD = "123"
Dim i As Long
If Target.Column = 8 And Target.Row > 3 Then
If Cells(Target.Row, "K").Value <> 0 Then
If MsgBox("Add new row?", vbYes) Then
Updating = True
ActiveSheet.Unprotect Password:=SALES_PWD
Rows(ActiveCell.Row & ":" & ActiveCell.Row).Insert
Cells(ActiveCell.Row, ActiveCell.Column) = Cells(ActiveCell.Row + 1, ActiveCell.Column) Cells(ActiveCell.Row + 1, ActiveCell.Column) = ""
For i = 1 To 5
Cells(ActiveCell.Row, i).Formula = Cells(ActiveCell.Row - 1, i).Formula
Next i
CopyCells 7
CopyCells 9
CopyCells 10
CopyCells 11
Cells(ActiveCell.Row, 8).Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=SALES_PWD
Updating = False
End If
End If
End If
End Sub
Sub CopyCells(ColNum As Long)
Cells(ActiveCell.Row - 1, ColNum).Select
Selection.Copy
Cells(ActiveCell.Row + 1, ColNum).Select
ActiveSheet.Paste
End Sub
thanking you in advance
regards
vipul jain
i have made a sheet with automatic row insertion if column f is less than column h. now i want that whenever the row is inserted than a formula should be entered in the row which is inserted below with formula (column f - column h) of the above row .
Example:-say if i have entered in row 4 of column f value 10 and row 4 of column h value 9 than a row is automatically inserted just below row 4 , now i want that in new row which is inserted just below column 4 say Row 5 is inserted , in that row 5 column f i want a formula that is f4-h4 so that the balance value may come
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const SALES_PWD = "123"
Dim i As Long
If Target.Column = 8 And Target.Row > 3 Then
If Cells(Target.Row, "K").Value <> 0 Then
If MsgBox("Add new row?", vbYes) Then
Updating = True
ActiveSheet.Unprotect Password:=SALES_PWD
Rows(ActiveCell.Row & ":" & ActiveCell.Row).Insert
Cells(ActiveCell.Row, ActiveCell.Column) = Cells(ActiveCell.Row + 1, ActiveCell.Column) Cells(ActiveCell.Row + 1, ActiveCell.Column) = ""
For i = 1 To 5
Cells(ActiveCell.Row, i).Formula = Cells(ActiveCell.Row - 1, i).Formula
Next i
CopyCells 7
CopyCells 9
CopyCells 10
CopyCells 11
Cells(ActiveCell.Row, 8).Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=SALES_PWD
Updating = False
End If
End If
End If
End Sub
Sub CopyCells(ColNum As Long)
Cells(ActiveCell.Row - 1, ColNum).Select
Selection.Copy
Cells(ActiveCell.Row + 1, ColNum).Select
ActiveSheet.Paste
End Sub
thanking you in advance
regards
vipul jain