Problem with vba routine

[The Tamer]

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

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

[Jacob Hilderbrand]

Where do you tell VBA that there is an error handler? You need to use an On Error Goto statement.

As to the code you can deal with each cell in the Target. Something like this:

Option Explicit
   
   Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel             As Range
For Each Cel In Target
'Code Here
Next
End Sub

Then just use Cel in place of Target in your code.

[Killian]

Hi,
Can you attach a sample workbook? It's a bit tricky to tell whats going on here...
I think you might be better managing your logic with a Select Case construct. GoTo is very confusing, especially when trying to trap errors.
Example:

Select Case Target.Column
Case 1 to 4
    'do stuff
Case 5 to 8
    'do other stuff etc
Case Else
    'you might want to have a message or reset code for unexpected selections
    MsgBox("Unhandled Case - Column: "  & Target.Column)
End Select

I must say though, normally I've done training schedules etc using data validation where the user picks a course from a drop down list in the cell and the formula autofills the rest of the data for that course. If there are more complex options, I would normally then make a user form form data selection/entry

[The Tamer]

Thanks both for replying.

Jake, when I try your solution I get the following message: "Compile error - duplicate declaration in current scope"

Killian, How do I send you a copy of the workbook?

[Jacob Hilderbrand]

You must have a duplicate Dim statement in your code. Check it again and remove the duplicate and you should be ok.

To attach the workbook just press Post Reply (or Go Advanced) to get to the reply page. Then scroll down below where you can enter your message and press Manage Attachments.

The attachment needs to be a zip file so just zip your workbook and attach it to your message.

[Zack Barresse]

Btw, when creating zip files, if you have windows 2000 or above (don't have 97 to check) you can right click your file, select Send To --> Compressed Zip file. No need for 3rd party compression programs.

[The Tamer]

Ok, here's the WB.

If you type in a name in the delegate column you'll see that the info to the left is filled in according to the row above. If you want to start a new course entry you just type the course info before you name any delegates. But copy and paste fails - as does drag and drop.

Thanks for your help on this

[TonyJollans]

Your problem is caused, not directly by copying and pasting, but by the Target being multiple cells. The line ..

If Target.Offset(, -7) <> "" Or ....

errors unless Target is a single cell. You need to work with each cell (or perhaps each row) within the target.

[The Tamer]

Thanks all...

Ok, that's what DRJ said too (i think). But when I apply the With Each Cel suggestion I get a "Compile error - duplicate declaration in current scope". Jake, you suggest there is a duplicate DIM statement, but I can't find one. Any ideas??

[The Tamer]

...So I was thinking of keeping that code as it is, but putting a new Error handler that would do a "one line at a time loop" until ther were no more delegate names. But vba still isn't my forte - most of my code comes from help forums or is bought - and I have a tricky time working it out when it gets more complex than what the recorder can do.

[Jacob Hilderbrand]

Ok, if you delete all your code and replace with this code does it compile and work?

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel             As Range
Application.ScreenUpdating = False
For Each Cel In Target
        If Cel.Column <> 12 Then
            GoTo Line2
        Else
            GoTo Line1
        End If
Line1:
ActiveCell.Offset(1, -5).Activate
        ActiveWindow.LargeScroll ToRight:=-1
Line2:
If Cel.Column <> 4 Then GoTo Line3
        If Cel.Offset(, -3) <> "" Or _
            Cel.Offset(, -2) <> "" Or _
            Cel.Offset(, -1) <> "" Or _
            Cel.Cells.Count > 1 Or _
            Cel.Value = "" Then
            Exit Sub
        End If
        Cel.Offset(, -3).Value = Cel.Offset(-1, -3).FormulaR1C1
        Cel.Offset(, -2).Value = Cel.Offset(-1, -2).FormulaR1C1
        Cel.Offset(, -1).Value = Cel.Offset(-1, -1).FormulaR1C1
Line3:
If Cel.Column <> 8 Then Exit Sub
        If Cel.Offset(, -7) <> "" Or _
            Cel.Offset(, -6) <> "" Or _
            Cel.Offset(, -5) <> "" Or _
            Cel.Offset(, -4) <> "" Or _
            Cel.Offset(, -3) <> "" Or _
            Cel.Offset(, -2) <> "" Or _
            Cel.Offset(, -1) <> "" Or _
            Cel.Cells.Count > 1 Or _
            Cel.Value = "" Then
            Exit Sub
        End If
        Cel.Offset(, -7).Value = Cel.Offset(-1, -7).FormulaR1C1
        Cel.Offset(, -6).Value = Cel.Offset(-1, -6).FormulaR1C1
        Cel.Offset(, -5).Value = Cel.Offset(-1, -5).FormulaR1C1
        Cel.Offset(, -4).Value = Cel.Offset(-1, -4).Value
        Cel.Offset(, -3).Value = Cel.Offset(-1, -3).Value
        Cel.Offset(, -2).Value = Cel.Offset(-1, -2).Value
        Cel.Offset(, -1).Value = Cel.Offset(-1, -1).Value
Next
End Sub

[The Tamer]

Jake

That works - Thanks a ton! What was wrong? - Where was my duplicate statement?

[TonyJollans]

Another point ...

Just looking at the code again to see if I could see your problem (and failing), I realise that you change the worksheet in the worksheet change event which leads to recursive calls which is not generally a good idea. You should put ..

Application.EnableEvents = False

.. at the start, and ..

Application.EnableEvents = True

.. at the end.

[Jacob Hilderbrand]

Jake

That works - Thanks a ton! What was wrong? - Where was my duplicate statement?

You're Welcome

I don't know what the problem was. I get the error you describe when I dim the same variable twice. Maybe you had some additional code that you missed?

But its working now, so that's good.

[Jacob Hilderbrand]

you change the worksheet in the worksheet change event which leads to recursive calls which is not generally a good idea.

Yes, it is definitely a good idea to avoid infinite loops.