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

Selasa, 07 April 2015

[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

End Sub

__._,_.___

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 (1)

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