Selasa, 07 April 2015

[ExcelVBA] Auto-populate a cell based on several other values



This should be simple, I have the basics, but I just can’t get the rest to fit.

The idea is to "auto-populate" a cell, based on the values that are in several others.  There is also a level of priority for each result, which must be adhered to.

How it looks is:
Column A = Action
Column B = Action number – 1…
Column C = Action result – Pass / Fail / Blocked / Ignored
Column D = Overall result – Pass / Fail / Blocked / Ignored

Now, how it should work is:
Column A contains a list of actions that should be performed.
Column B is the number of the action in the sequence of actions.  This can be from 1 to any number of actions.
Column C is the result of the action.  
Column D is the overall result of the actions.

So, a sequence of actions can contain 20 steps.  Each step must pass, so the overall result is a pass.
However if, the same set of actions, one of the actions fails, the overall action fails.

There may also be the situation where multiple step actions exists, and the overall action should be set based on a priority level.  The priority level is…Pass, Fail, Blocked, Ignored.  So, if step 2 passes, step 5 fails, step 8 is blocked and step 10 is a pass, the overall result should be a fail.  This is because step results appear within the range, that are not a pass.

This process should continue until an action number of 1 is reached, and the checking begins from scratch again.

I've possibly not explained it too well.  Hope this makes it easier to understand:

Column 1     Column B     Column C     Column D
Step 1           1                    Pass               Pass
Step 2           2                    Pass
Step 3           3                    Pass
Step 10         1                    Pass               Fail
Step 11         2                    Pass
Step 12         3                    Blocked
Step 13         4                    Fail
Step 14         5                    Ignored
Step 15         6                    Pass

In the second example, since there is a fail step the action as a whole fails.  Even although there are also Blocked, Ignored and Pass steps, a Fail is recorded.  If the fail wasn’t there, the action as a whole would be Blocked.  Here’s the code I’ve got, and it is very much a work in progress.  It doesn’t work, but I’ve no idea what it needs to get working. 

Can anyone help?


Sub PopulatePass()

Const pPass = 4
Const pFail = 3
Const pBlocked = 2
Const pDeScoped = 1
Const Column_G = 7
Const Column_K = 11
Const Column_M = 13

Dim RowNum As Integer
Dim WS As Worksheet
Dim Priority As Byte
    StartRow = 9
    RowNum = 9
    Set WS = Worksheets("1.11")

    With WS.UsedRange
        NumRows = .Rows.Count
    End With
    Priority = pPass
    tmpPriority = pPass
    ResultText = WS.Cells(RowNum, Column_K)

    For i = 9 To NumRows

        ResultText = WS.Cells(RowNum, Column_K)
        If ResultText <> "Pass" Then

            Select Case ResultText
                Case "Fail"
                    Priority = pFail
                Case "Blocked"
                    Priority = pBlocked
                Case Else
                    Priority = pDeScoped
            End Select
        End If
        If Priority < tmpPriority Then
            tmpPriority = Priority
        End If
        Select Case Priority
            Case 1:
                WS.Cells(StartRow, Column_M) = "De-Scoped"
            Case 2:
                WS.Cells(StartRow, Column_M) = "Blocked"
            Case 3:
                WS.Cells(StartRow, Column_M) = "Fail"
            Case 4:
                WS.Cells(StartRow, Column_M) = "Pass"
        End Select
        RowNum = RowNum + 1
        tmpPriority = Priority
        If WS.Cells(RowNum, Column_G) = 1 Then
            StartRow = RowNum
            Priority = pPass
            tmpPriority = pPass
        End If

End Sub


Posted by: Duncan Edment <>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: to enter the ezine, then search the ARCHIVES for EXCEL VBA.

Visit our ExcelVBA group home page for more info and support files:

More free tutorials and resources available at:




Tidak ada komentar:

Poskan Komentar