Sorry Lisa. I think I was a wee bit trigger happy when I sent it. The code I had was more or less "finger in the air", hence the state of it.
I'll have a look at it tonight and tidy it up.
Er.... Maybe some comments?? :-)
From: Duncan Edment firstname.lastname@example.org [ExcelVBA] <ExcelVBA@yahoogroups.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Wed, Apr 8, 2015 5:43 pm
Subject: 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.DuncanOn 8 Apr 2015 13:34, "'David Smart' email@example.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 wrongFor i = 9 To NumRowsNumRows is 9 for your example data, so the loop will only be done once, I'm sure you wantFor i = 1 To NumRowsAlso, I suspect that your priority constants are in the wrong order. You do a checkIf Priority < tmpPriority Thento 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 withIf WS.Cells(RowNum, Column_G) = 1 Thenbut 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 AMSubject: [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 = ActionColumn B = Action number – 1…Column C = Action result – Pass / Fail / Blocked / IgnoredColumn 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 DStep 1 1 Pass PassStep 2 2 PassStep 3 3 PassStep 10 1 Pass FailStep 11 2 PassStep 12 3 BlockedStep 13 4 FailStep 14 5 IgnoredStep 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?
Const pPass = 4Const pFail = 3Const pBlocked = 2Const pDeScoped = 1Const Column_G = 7Const Column_K = 11Const Column_M = 13
Dim RowNum As IntegerDim WS As WorksheetDim Priority As ByteStartRow = 9RowNum = 9Set WS = Worksheets("1.11")
With WS.UsedRangeNumRows = .Rows.CountEnd WithPriority = pPasstmpPriority = pPassResultText = WS.Cells(RowNum, Column_K)
For i = 9 To NumRows
ResultText = WS.Cells(RowNum, Column_K)If ResultText <> "Pass" Then
Select Case ResultTextCase "Fail"Priority = pFailCase "Blocked"Priority = pBlockedCase ElsePriority = pDeScopedEnd SelectEnd IfIf Priority < tmpPriority ThentmpPriority = PriorityEnd IfSelect Case PriorityCase 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 SelectRowNum = RowNum + 1tmpPriority = PriorityIf WS.Cells(RowNum, Column_G) = 1 ThenStartRow = RowNumPriority = pPasstmpPriority = pPassEnd If
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 <firstname.lastname@example.org>
|Reply via web post||•||Reply to sender||•||Reply to group||•||Start a New Topic||•||Messages in this topic (7)|
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: