Hi All,

I have written a routine that matches 2 staff lists that may contain up to
5000 staff and the exercise needs to be repeated approx 140 times so you can
see the neccesity of automating the process. However there are the
inevitable unmatched entry's which need manual intervention. The matching
cannot be done on surname only as there are many duplicates.

1) This is an example of the unmatched entries before manual intervention

A B C D E F
G H I J
1 SMITHWILL SMITH WILL xyz abc
2
SMITHWILLIAM SMITH WILLIAM xyz abc

2) This is how it needs to look after manual intervention

A B C D E F
G H I J
1 SMITHWILL SMITH WILL xyz abc SMITHWILL SMITH
WILLIAM xyz abc

This was achieved by clicking on SMITHWILLIAM and using a keyboard shortcut
to run the following code (there are many more columns associated to
smithwilliam in the actual template) then dragging the whole selection up 1
row.

3) Range(ActiveCell, ActiveCell.Offset(0, 12)).Select

Secondly, and essentially, so that columns A & F are an exact match, the
following code is run by kb shortcut

4) ActiveCell.Formula = "=UPPER(A" & ActiveCell.Row & ")"

The problem with that is the user is very likely to forget to run the 2nd
bit of code.

So what l am trying to achieve is this:
1) The user selects SMITHWILLIAM and runs code to select all associated
columns, as per code in 3)
2) The user drags the selected range to the appropriate row
3) Code is automatically run to copy contents of column A to column F, as
per code in 4)

It seems to me that using the worksheet_change event is not appropriate
because of the number cells being changed by the various other routines that
copy, paste, move, match etc.

Hope l have explained the problem clearly.

All suggestions / possible solutions gratefully recieved

Regards

Michael Beckinsale

There is a way you could do this with the Worksheet_Change event if you use
Application.EnableEvents = False to switch off the events (and then remember
to turn it back on at the end!). You would also need some way of knowing
whether the current change is one that should trigger the second sub to run
(because the user may have changed some other part of the sheet). One way to
do that would be to use a Global variable "flag" (boolean) and have the first
sub set it equal to True. The second sub would check to see if it is true
before it would run; e.g:

Public RunMySub as Boolean

Sub Worksheet_Change()
FixName
End Sub

Sub MoveSelection() ' i.e. your first sub
' your existing code would be here
RunMySub = True
End Sub

Sub FixName() ' i.e. your second sub
On Error Goto Err
' Switch off the events so that the change in ActiveCell will not cause
' a recursive call back to this Sub!
Application.EnableEvents = False
If RunMySub Then
ActiveCell.Formula = "=UPPER(A" & ActiveCell.Row & ")"
End If
Err:
RunMySub = False
Application.EnableEvents = True
End Sub

I would highly recommend the error handler as shown so that any problem in
the second sub switches the flag off; otherwise it could run again and be
triggered to keep running, and to make sure you re-enable the events.

Hope I got the code right (no time to test!) and HTH!
--
- K Dales


"Michael Beckinsale" wrote:

Hi All,

I have written a routine that matches 2 staff lists that may contain up to
5000 staff and the exercise needs to be repeated approx 140 times so you can
see the neccesity of automating the process. However there are the
inevitable unmatched entry's which need manual intervention. The matching
cannot be done on surname only as there are many duplicates.

1) This is an example of the unmatched entries before manual intervention

A B C D E F
G H I J
1 SMITHWILL SMITH WILL xyz abc
2
SMITHWILLIAM SMITH WILLIAM xyz abc

2) This is how it needs to look after manual intervention

A B C D E F
G H I J
1 SMITHWILL SMITH WILL xyz abc SMITHWILL SMITH
WILLIAM xyz abc

This was achieved by clicking on SMITHWILLIAM and using a keyboard shortcut
to run the following code (there are many more columns associated to
smithwilliam in the actual template) then dragging the whole selection up 1
row.

3) Range(ActiveCell, ActiveCell.Offset(0, 12)).Select

Secondly, and essentially, so that columns A & F are an exact match, the
following code is run by kb shortcut

4) ActiveCell.Formula = "=UPPER(A" & ActiveCell.Row & ")"

The problem with that is the user is very likely to forget to run the 2nd
bit of code.

So what l am trying to achieve is this:
1) The user selects SMITHWILLIAM and runs code to select all associated
columns, as per code in 3)
2) The user drags the selected range to the appropriate row
3) Code is automatically run to copy contents of column A to column F, as
per code in 4)

It seems to me that using the worksheet_change event is not appropriate
because of the number cells being changed by the various other routines that
copy, paste, move, match etc.

Hope l have explained the problem clearly.

All suggestions / possible solutions gratefully recieved

Regards

Michael Beckinsale