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

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.

Thanks.

Duncan

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 -----

From: Duncan Edment duncan.edment@gmail.com [ExcelVBA]

To: ExcelVBA@yahoogroups.com

Sent: Wednesday, April 08, 2015 7:57 AM

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

Folks,

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?

Duncan

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

    Next



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:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group