• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

Enhancing a RASCI Model to avoid different input to same assigned Priority

EE Professionals,

I have a very nice workbook that several EE Professionals wrote for use;  I have two problems with it.  One is a design problem that I didn't anticipate and was my fault by not indicating it in the specifications.  The second issue is a design issue for output, which I will post in a new related question once the first issue is resolved.  I have attached the actual code in the file and I believe the modification I am looking at is in Sheet 1 of the VBA Window.

Overview of Problem:

The RASCI Model is a Project Management tool that simply allows you to identify a set of tasks, put them in priority sequence and display who has responsibility for what.  The model attached works fine EXCEPT when you add an additional RESPONSIBILITY to the same task (by adding it to the first blank cell in column A) you can get erroneous results if the Text doesn't exactly match or the user inadvertently adds different text to the same priority number.  So for example, if I were to put in "1.1" in cell A (first open cell in column), and 1.1 was already a Task, the addition of another assignment to the same task, 1.1, should display THE SAME TASK.  This is so you can assign another responsible Functional Area to the same Task.  It allows this now so when you put in 1.1, it will combine the results on the Output beginning in Column H.

What I'm trying to accomplish:

In order to avoid mistakes and bad results, I'd like for the macro to check to see if the assigned number exists, and if so, is auto populate with the previous description IF and only if, a duplicate number appears. This will prevent the user from putting in a different task for the same assigned priority.  So, if I put in 1.1, and 1.1 already exists, it will duplicate the text description in Cell B.  That's it!
Copy-of-RASCI-Sheetv76-Sample-r6.xlsm
0
Bright01
Asked:
Bright01
  • 2
1 Solution
 
Arno KosterCommented:
You could use this :

    '-- auto populate existing processes on new entry
    If Target.Column = 1 Then
        If Target.Row > glOldRows Then
            Set result = Range("A4:A" & Target.Row - 1).Find(Target, lookat:=xlWhole)
                If Not result Is Nothing Then
                    Application.EnableEvents = False
                    '-- fill in process name
                    Target.Offset(0, 1) = result.Offset(0, 1)
                    Application.EnableEvents = True
                End If
        End If
    End If

Open in new window


as in  Copy-of-RASCI-Sheetv76-Sample-r6.xlsm

be sure though to thoroughly test all regular use scenario's (does it work as you expect when updating rows, deleting rows etc)
0
 
Bright01Author Commented:
Akoster,

I tested the workbook and it works very well.   I believe that any of the slight issues are probably a function of having the table on the same set of rows as the input.  I think if I have the output placed on a different tab/sheet, I think it will ease some of the complexity that surrounds this set of Macros.  I'll be asking a related question to try to move the output to another tab.

I appreciate your insight and help with this.

B.
0
 
Arno KosterCommented:
No problem !
glad to have been of service...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now