VB code to skip rows and keep formula immovable

sshrikanth2

Board Regular
Joined
Jan 17, 2012
Messages
138
Hi,

I need your help...

I have a VB code which skips the row from Column 20 to Column 67 but i have a formula in "Column Z" and "Column AA" respectively. When i execute the skip row function the formula will move one step down vertically and the upper cell or row remains blank but i want the formula remain as it is even though i perform skip function. I appreciate your help.

Formula - Column Z4:Z5000 =IF(SUM(L4:Y4)>0,SUM(L4:Y4),0)
Column AA4:AA5000 =IF(Z4=0,0,"1")



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Task As String
Dim Cell_To_Test As Range, Cells_Changed As Range
Dim Email, TechName, Remarks As Integer

Set Cells_Changed = Target(1, 1)
Set Cell_To_Test = Range("C4:C100000")
If Not Intersect(Cells_Changed, Cell_To_Test) Is Nothing Then
ActiveWorkbook.Worksheets("Quality Check Log").Unprotect Password:="RAJU"
cellrow = ActiveCell.Row
cellcolumn = ActiveCell.Column
'Range(ActiveCell, 27).Select
If ActiveCell.Value <> "QualityCheck" Then
Cells(cellrow, 20).Select
Range(ActiveCell, ActiveCell.Offset(i, 47)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Cells(CellRow, 43).Select
'Range(ActiveCell, ActiveCell.Offset(i, 0)).Select
'ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
'Selection.Copy
'ActiveCell.Offset(1, 0).Select
'Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Range(ActiveCell, ActiveCell.Offset(i, 0)).Select
ActiveCell.Offset(-1, 0).Select
Range(ActiveCell, ActiveCell.Offset(i, 47)).Select
Selection.ClearContents
Cells(cellrow, cellcolumn).Select
ActiveWorkbook.Worksheets("Quality Check Log").Protect Password:="RAJU"
Else
Cells(cellrow, 5).Value = Cells(cellrow, 67).Value
ActiveWorkbook.Worksheets("Quality Check Log").Protect Password:="RAJU"
End If
End If
End Sub


Regards,

Srikanth M :)
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Watch MrExcel Video

Forum statistics

Threads
1,133,529
Messages
5,659,343
Members
418,498
Latest member
nattynat

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
Top