PDA

View Full Version : Solved: Problem with vba routine



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

Jacob Hilderbrand
12-07-2004, 03:10 AM
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
12-07-2004, 03:32 AM
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
12-07-2004, 08:56 AM
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
12-07-2004, 09:06 AM
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
12-07-2004, 09:28 AM
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
12-07-2004, 09:29 AM
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 :hi:

TonyJollans
12-08-2004, 04:20 AM
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
12-08-2004, 04:43 AM
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
12-08-2004, 04:48 AM
...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
12-08-2004, 04:52 AM
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
12-08-2004, 05:46 AM
Jake

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

TonyJollans
12-08-2004, 05:51 AM
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
12-08-2004, 06:02 AM
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
12-08-2004, 06:04 AM
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. :eek: