georgedaws
03-20-2011, 08:27 AM
Hi,
I have this code that works fine
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(3))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("A" & i - 1 & ":B" & i - 1).Copy Range("A" & i & ":B" & i)
Application.EnableEvents = True
On Error GoTo 0
End Sub
But I need this code to work alongside the same code but with a different target column and range as below
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(16))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("Q" & i - 1 ).Copy Range("Q" & i &)
Application.EnableEvents = True
On Error GoTo 0
End Sub
To make it slightly more complicated, I already have this code existing as a worksheet change
Private Sub Worksheet_Change(ByVal Target As Range)
Const sWSPWD As String = ""
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("F3:F10000")) Is Nothing Then
Me.Unprotect ""
If Target.Offset(, 2) = "" Then Target.Offset(, 2) = Date
Me.Protect ""
ElseIf Not Intersect(Target, Me.Range("L3:L10000")) Is Nothing Then
ActiveSheet.Unprotect ""
If Target.Offset(, 1) = "" Then Target.Offset(, 1) = Date
ActiveSheet.Protect ""
ElseIf Not Intersect(Target, Me.Range("O3:O10000")) Is Nothing Then
ActiveSheet.Unprotect ""
If Target.Offset(, 1) = "" Then Target.Offset(, 1) = Date
ActiveSheet.Protect ""
ElseIf Not Intersect(Target, Me.Range("T3:T10000")) Is Nothing Then
ActiveSheet.Unprotect ""
If Target.Offset(, 1) = "" Then Target.Offset(, 1) = Date
ActiveSheet.Protect ""
End Sub
So I just need all three to work in harmony.
I have tried inserting "end If" and "else if" at differnt points I thought they should go, but my knowledge is very limited.
I need vba to insert formula to next row because of the amount of rows that will be used.
If it can't be done and you read this, please just let me know.
Thank you
I have this code that works fine
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(3))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("A" & i - 1 & ":B" & i - 1).Copy Range("A" & i & ":B" & i)
Application.EnableEvents = True
On Error GoTo 0
End Sub
But I need this code to work alongside the same code but with a different target column and range as below
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(16))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("Q" & i - 1 ).Copy Range("Q" & i &)
Application.EnableEvents = True
On Error GoTo 0
End Sub
To make it slightly more complicated, I already have this code existing as a worksheet change
Private Sub Worksheet_Change(ByVal Target As Range)
Const sWSPWD As String = ""
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("F3:F10000")) Is Nothing Then
Me.Unprotect ""
If Target.Offset(, 2) = "" Then Target.Offset(, 2) = Date
Me.Protect ""
ElseIf Not Intersect(Target, Me.Range("L3:L10000")) Is Nothing Then
ActiveSheet.Unprotect ""
If Target.Offset(, 1) = "" Then Target.Offset(, 1) = Date
ActiveSheet.Protect ""
ElseIf Not Intersect(Target, Me.Range("O3:O10000")) Is Nothing Then
ActiveSheet.Unprotect ""
If Target.Offset(, 1) = "" Then Target.Offset(, 1) = Date
ActiveSheet.Protect ""
ElseIf Not Intersect(Target, Me.Range("T3:T10000")) Is Nothing Then
ActiveSheet.Unprotect ""
If Target.Offset(, 1) = "" Then Target.Offset(, 1) = Date
ActiveSheet.Protect ""
End Sub
So I just need all three to work in harmony.
I have tried inserting "end If" and "else if" at differnt points I thought they should go, but my knowledge is very limited.
I need vba to insert formula to next row because of the amount of rows that will be used.
If it can't be done and you read this, please just let me know.
Thank you