The Tamer
12-07-2004, 02:15 AM
Hi,
Can someone please help me with this code? It?s a continuation of this thread: http://www.mrexcel.com/board2/viewtopic.php?t=117293 (http://www.mrexcel.com/board2/viewtopic.php?t=117293)
The code copies formulas and values from the row above once the user enters new information in a certain column. (i.e. the user enters a delegate name, and the course info from the cells to the left are automatically entered).
The code works fine until the user decides to paste more than one name. (which he might do if the same group of people attend more than one course).
So the code so far is:
Private Sub Worksheet_Change(ByVal Target As Range)
'This tries to establish whether a person is putting in a new course and_
'copies the formulae in columns A & B
Application.ScreenUpdating = False
If Target.Column <> 12 Then GoTo Line2 Else GoTo Line1
Line1:
ActiveCell.Offset(1, -5).Activate
ActiveWindow.LargeScroll ToRight:=-1
Line2:
If Target.Column <> 4 Then GoTo Line3
If Target.Offset(, -3) <> "" Or Target.Offset(, -2) <> "" _
Or Target.Offset(, -1) <> "" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Target.Offset(, -3).Value = Target.Offset(-1, -3).FormulaR1C1
Target.Offset(, -2).Value = Target.Offset(-1, -2).FormulaR1C1
Target.Offset(, -1).Value = Target.Offset(-1, -1).FormulaR1C1
Line3:
If Target.Column <> 8 Then Exit Sub
If Target.Offset(, -7) <> "" Or Target.Offset(, -6) <> "" _
Or Target.Offset(, -5) <> "" Or Target.Offset(, -4) <> "" _
Or Target.Offset(, -3) <> "" Or Target.Offset(, -2) <> "" _
Or Target.Offset(, -1) <> "" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Target.Offset(, -7).Value = Target.Offset(-1, -7).FormulaR1C1
Target.Offset(, -6).Value = Target.Offset(-1, -6).FormulaR1C1
Target.Offset(, -5).Value = Target.Offset(-1, -5).FormulaR1C1
Target.Offset(, -4).Value = Target.Offset(-1, -4).Value
Target.Offset(, -3).Value = Target.Offset(-1, -3).Value
Target.Offset(, -2).Value = Target.Offset(-1, -2).Value
Target.Offset(, -1).Value = Target.Offset(-1, -1).Value
ErrorHandler:
Exit Sub
End Sub
But on copy and paste I was getting "Runtime error 13: Type Mismatch" (until I put the error handler in ? but that doesn?t fix the problem, it only ends the macro)
Can anyone help please?
Thanks
Can someone please help me with this code? It?s a continuation of this thread: http://www.mrexcel.com/board2/viewtopic.php?t=117293 (http://www.mrexcel.com/board2/viewtopic.php?t=117293)
The code copies formulas and values from the row above once the user enters new information in a certain column. (i.e. the user enters a delegate name, and the course info from the cells to the left are automatically entered).
The code works fine until the user decides to paste more than one name. (which he might do if the same group of people attend more than one course).
So the code so far is:
Private Sub Worksheet_Change(ByVal Target As Range)
'This tries to establish whether a person is putting in a new course and_
'copies the formulae in columns A & B
Application.ScreenUpdating = False
If Target.Column <> 12 Then GoTo Line2 Else GoTo Line1
Line1:
ActiveCell.Offset(1, -5).Activate
ActiveWindow.LargeScroll ToRight:=-1
Line2:
If Target.Column <> 4 Then GoTo Line3
If Target.Offset(, -3) <> "" Or Target.Offset(, -2) <> "" _
Or Target.Offset(, -1) <> "" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Target.Offset(, -3).Value = Target.Offset(-1, -3).FormulaR1C1
Target.Offset(, -2).Value = Target.Offset(-1, -2).FormulaR1C1
Target.Offset(, -1).Value = Target.Offset(-1, -1).FormulaR1C1
Line3:
If Target.Column <> 8 Then Exit Sub
If Target.Offset(, -7) <> "" Or Target.Offset(, -6) <> "" _
Or Target.Offset(, -5) <> "" Or Target.Offset(, -4) <> "" _
Or Target.Offset(, -3) <> "" Or Target.Offset(, -2) <> "" _
Or Target.Offset(, -1) <> "" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Target.Offset(, -7).Value = Target.Offset(-1, -7).FormulaR1C1
Target.Offset(, -6).Value = Target.Offset(-1, -6).FormulaR1C1
Target.Offset(, -5).Value = Target.Offset(-1, -5).FormulaR1C1
Target.Offset(, -4).Value = Target.Offset(-1, -4).Value
Target.Offset(, -3).Value = Target.Offset(-1, -3).Value
Target.Offset(, -2).Value = Target.Offset(-1, -2).Value
Target.Offset(, -1).Value = Target.Offset(-1, -1).Value
ErrorHandler:
Exit Sub
End Sub
But on copy and paste I was getting "Runtime error 13: Type Mismatch" (until I put the error handler in ? but that doesn?t fix the problem, it only ends the macro)
Can anyone help please?
Thanks