Rabu, 08 April 2015

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


David, sorry for the confusion. I was trying to remove company details, but forgot to either update the code or leave the rows / data as they were.

I'll get it updated, using your suggestions, and let you know.



On 8 Apr 2015 13:34, "'David Smart' smartware.consulting@gmail.com [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:

It is impossible to work out what your code is trying to do when it doesn't even match your data.  Your data has a column 1 (do you mean A?) and columns B, C and D.
But your code has columns G, K and M and M appears to be an output column.  Completely different.
Please re-post with code that actually matches your data and description.
That said, this statement is clearly wrong
  For i = 9 To NumRows
NumRows is 9 for your example data, so the loop will only be done once,  I'm sure you want
 For i = 1 To NumRows
Also, I suspect that your priority constants are in the wrong order.  You do a check
        If Priority < tmpPriority Then
to work out if there is a more important item (e.g. Fail) to override a lesser item (e.g. Blocked).  Therefore pFail should be less than pBlocked.  In fact it should be 1 because it overrides everything else.
You also look for the start of a new group with
    If WS.Cells(RowNum, Column_G) = 1 Then
but you have added 1 to RowNum before that, so it doesn't find it.  In fact, that check should be the very first thing you do inside the loop, not almost the last.
There are other problems in the code too, by the look of it, but these ones should be enough to get it nearly working.  Try them out and then post again if you still have trouble ... but make sure that your code matches your data.

Regards, Dave S
----- Original Message -----
Sent: Wednesday, April 08, 2015 7:57 AM
Subject: [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

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2015.0.5863 / Virus Database: 4321/9483 - Release Date: 04/07/15


Posted by: Duncan Edment <duncan.edment@gmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax 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:

Posting Komentar