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.
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 valuesFolks,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 / IgnoredNow, 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 PassIn 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?DuncanSub PopulatePass()Const pPass = 4Const pFail = 3Const pBlocked = 2Const pDeScoped = 1Const Column_G = 7Const Column_K = 11Const Column_M = 13Dim 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 NumRowsResultText = WS.Cells(RowNum, Column_K)If ResultText <> "Pass" ThenSelect 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 IfNextEnd 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 <firstname.lastname@example.org>
|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: