Thanks for taking the time to look Roy,
There are 2 sheets pertinent to the code. HTL and Tracking.
StatusRng is on the HTL sheet. The Tracking sheet is hidden and will not be used in everyday work, it is trying to capture dates of events to build up a timeframe of work passing though the facility.
Vlookup is taking the selected cell in StatusRng and looking across to pick up the order number. In the attached, simplified workbook, that is col C.
The order number is a constant allocated by the sales team. On occasion there will be no order number and these should be ignored which is why I put in the If IsEmpty statement.
I have attached a cut down workbook. When a status is applied in the HTL sheet the code does the following:
- Check if order number exists - if no then exit
- If order number exists then check Tracking sheet for that order number
- If order number does not exist on Tracking sheet, write to new row and enter date in relevant status column
- If order number does exist on Tracking sheet, find row and enter date in relevant status column.
Works fine on single changes however not on copy / paste of several rows. So if the user wants to change 10 rows to a status of Production he is changing the first one then copying that status and pasting to the remaining rows. That throws a type mismatch error on Ln73 of the code which is between the two Case statements.
CurrRow = Application.WorksheetFunction.Match(result, ws.Range("B:B"), 0)
In order to get things working again I need to stop the debugger and in the immediate window run Application.EnableEvents = True