PDA

View Full Version : Still Stuck On A loop



NWE
05-13-2019, 02:28 PM
Hello All,

So I am still struggling on getting the right answers for my loops. There are 2 pages that comprise the conditional loop. A Page called Detail Report and an As Built Page. The Detail Report, In column E contains the following answers: "Audit Error" and " Detail Error" and "". In Column C, contains things such as "PR Code". In column A or B, it contains an address to the cell that was marked in Column C.I would like the following loop to fulfill the following condition. If Column C says "PR Code" OR If Column L on "As Built" sheets = Column E on Detail report (either which would fulfill the condition), add up the numbers for those cells only from column I only. For example, if there are 3 matches found, it should only add up the corresponding numbers in column I for those matches. However my loops seem to add up random numbers, or multiply things by two or subtract 1 from the right answer etc. So any help on making this consistent would be appreciated. I feel like either I am using the wrong math function or I am still not isolating the cells correctly.



Sub PrError()
'This functions calculates pr qty if pr code is not a match.


For x = 1 To 10001
If Sheets("Detail Report").Range("C" & x) = "PR Code" Then

On Error Resume Next
splt = Split(Sheets("Detail Report").Range("A" & x), "")
fnd = splt(2)




If Sheets("As Built").Range("L" & fnd) = "Found" Then
Worksheets("Generalized Report").Range("C16").Value = Worksheets("Generalized Report").Range("C16").Value + WorksheetFunction.Sum(Worksheets("As Built").Range("I" & x).Value)
End If
End If
Next x
End Sub

Bob Phillips
05-13-2019, 03:09 PM
Your code does not seem to comply with your description.

If cell Cx = "PR Code", what gets added into the running total?

If cell Ax (you say E, the code says A) contains fnd, then we add all items column I of 'As Built' where column L says Found into the running total? Won't that mean those values get added multiple times?

NWE
05-13-2019, 03:57 PM
If cell Cx on "Detail report" = "Pr Code" then the loop should look at the the corresponding column "I" on "As Built" associated with that "Pr Code" and add those numbers together. Would it be easier to say something like:


For x=1 to 100001
If sheets("Detail Report").Range("E" & x) = Sheets("As Built").Range("L" & X) then
Worksheets("Generalized Report").Range("C16").Value = Worksheets("Generalized Report").Range("C16").Value + WorksheetFunction.Sum(Worksheets("As Built").Range("I" & x).Value)
End If


The value in Column E on Detail Report would match the value in Column L on As built to execute the loop. I have tried it both ways and I still run into an issue that adds multiple times or adds wrong.

大灰狼1976
05-13-2019, 11:24 PM
Hi NWE!
There should be a better way. Please upload an attachment.


--Okami

Bob Phillips
05-14-2019, 03:25 AM
If cell Cx on "Detail report" = "Pr Code" then the loop should look at the the corresponding column "I" on "As Built" associated with that "Pr Code" and add those numbers together.

Where on Detail is the PR Code that is to be looked up?

NWE
05-14-2019, 07:15 AM
I have uploaded an attachment of the detail report page.

"Pr Code" exists in column C (Along with other items, that may be used to replicate the process later)

Bob Phillips
05-14-2019, 11:25 AM
Come on mate, what use is that workbook? I assume that sheet is supposed to be Detail Report, but what about the other two? We can't help you if you obstruct us.

NWE
05-14-2019, 11:49 AM
Sorry my mistake mate, here is the entire workbook. It is module 1, sub PrError() and subAurErrQuant() are giving me the issues.

Bob Phillips
05-14-2019, 02:26 PM
I give up, even seeing this workbook I cannot figure out what is going on, what is wanted. I can see a column with the text PR Code, but where is the PR Code to lookup against AS Built? Your code makes no sense against the data I see.

p45cal
05-14-2019, 02:27 PM
Looking at PR Code only in column C of the Detail Report sheet, there are only 2 instances.
When the PrError macro has finished how much will it add to cell C16 of the Generalized Report sheet?

I reckon it could be 2, or maybe 22.
Is it one of these, or something else?
From your answer I might be able to guess what you're trying to do.

NWE
05-14-2019, 03:15 PM
xld: The string "Pr Code" in column C of "Detail Report" or the actual PR Code in Column E of "Detail Report" can be use to look at the same code in Column L on "As Built" and then add only the numbers that match that code (same row) in Column I on "As Built".

p45cal: Yes there are two instances of Pr Code when you filter. So those two instances have a corresponding code in Column E on "Detail Report" and column L on "As Built". The loop I attempted basically said: If column C on "detail report" = "Pr Code" OR column E on "detail Report" = Column L on "As Built" then look at column I on "As Built" and add only the numbers that correspond to those conditions. The number I should get for the current WB should be 2..but I get 5.

Did that make sense or am I still explaining it bad?

p45cal
05-14-2019, 04:53 PM
One way, and there are better ways:
Sub PrError2()
For x = 1 To 10001
If Sheets("Detail Report").Range("C" & x) = "PR Code" Then
If Worksheets("As Built").Range(Sheets("Detail Report").Range("A" & x).Value).Value = Sheets("Detail Report").Range("E" & x).Value Then
Worksheets("Generalized Report").Range("C16").Value = Worksheets("Generalized Report").Range("C16").Value + Worksheets("As Built").Range(Sheets("Detail Report").Range("A" & x).Value).Offset(, -3).Value
End If
End If
Next x
End Sub

Broken down a bit, can be re-written:
Sub PrError3()
Set CellToIncrement = Worksheets("Generalized Report").Range("C16")
For x = 1 To 10001
If Sheets("Detail Report").Range("C" & x) = "PR Code" Then
Set CellOnAsBuiltSheet = Worksheets("As Built").Range(Sheets("Detail Report").Range("A" & x).Value) 'This uses the cell ref in column A of the Detailed Report sheet to identify that cell on the As Built sheet.
If CellOnAsBuiltSheet.Value = Sheets("Detail Report").Range("E" & x).Value Then
CellToIncrement.Value = CellToIncrement.Value + CellOnAsBuiltSheet.Offset(, -3).Value
End If
End If
Next x
End Sub

Note the names of these macros are not quite your original ones - tweak as necessary.


ps. your Reset macro could be a bit shorter:
Sub reset()
'Resets Values on Generalized Report
With Worksheets("Generalized Report")
.Range("A4:D4,A6:D6,A8:D8,A10:D10,A12:C12,A14:D14,A16:D16,A18:D18,A20:D20,A22:B22,A 25:D25,A27,D22") = 0
.Range("A28") = ""
End With
End Sub

NWE
05-15-2019, 07:39 AM
Thank you all for your help on this!

p45cal: On the second code, can you explain to me what the offset does?, also thank you for the suggested sub edit for the reset values!

p45cal
05-15-2019, 08:34 AM
Let's say x= 25 and Cell A25 on the Detail Report sheet contains $L$38

Worksheets("As Built").Range(Sheets("Detail Report").Range("A" & x).Value).Offset(, -3).Value
becomes:
Worksheets("As Built").Range(Sheets("Detail Report").Range("A" & 25).Value).Offset(, -3).Value
becomes:
Worksheets("As Built").Range(Sheets("Detail Report").Range("A25").Value).Offset(, -3).Value
becomes:
Worksheets("As Built").Range("$L$38").Offset(, -3).Value
offset(,-3) means 3 cells to the left so it becomes:
Worksheets("As Built").Range("$I$38").Value

NWE
05-15-2019, 09:38 AM
I think I understand the offset approach. In your first code above, would changing the "A & x" to "B & X" be a way to tie the line items together? For example. If Column E says "Audit Error" on detail report, and I wanted to isolate the line item in column B. Then match that line item to Column A in As Built, then offset that value by +6 to add the quantities in column G (Line item sits in Column A on As Built, and Ex Quantity sits in Column G.)

p45cal
05-15-2019, 02:11 PM
Yes, you can do that, and that will be robust as long as there are never duplicate line item numbers in column A of As Built.

NWE
05-15-2019, 03:57 PM
So I changed the code to target Audit Error in Column E and offset (-16) so that basically, if Column E equals Audit Error, then it should calculate the corresponding column G on As built. Is it does it need to be a loop within a loop?

p45cal
05-15-2019, 04:22 PM
You could use a loop, or avoid one by using perhaps vlookup within the vba or range.find.
Hang on, if you're using offset(,-16) that implies that you're using column A of the Detail Report sheet, so no you won't need a loop; your code shouldn't be very different from the PR Code one.
What's your code for this so far?
Bed time here.

NWE
05-15-2019, 04:38 PM
So far I have


Sub PrError()
For x = 1 To 10001
If Sheets("Detail Report").Range("E" & x) = "Audit Error" Then
If If Sheets("Detail Report").Range("B" & x) = "" Then 'Line item connection
If Worksheets("As Built").Range(Sheets("Detail Report").Range("B" & x).Value).Value = Sheets("Detail Report").Range("B" & x).Value Then
Worksheets("Generalized Report").Range("C16").Value = Worksheets("Generalized Report").Range("A4").Value + Worksheets("Contract").Range(Sheets("Detail Report").Range("A" & x).Value).Offset(, +6).Value - Worksheets("As Built").Range(Sheets("Detail Report").Range("A" & x).Value).Offset(, +6).Value
End If
End If
Next x
End Sub


I want to say If Audit Error, Look at line item (Column B) match that line item to line item on "As Built" then if these are true, subtract the offset value of contract from the offset value of As built. So A + 6 is Column G which is ex quantity.

p45cal
05-17-2019, 06:25 AM
Scenario:
Cell A4 of the Generalized Report sheet starts of with 0 in it.
If for one Audit Error, there's a value of 1 in Column G of the Contract sheet, and a value of 6 in the corresponding cell in the As Built sheet, your code will return a -5 difference.
Cell A4 of the Generalized Report sheet will become -5
If for another Audit Error, there's a value of 6 in Column G of the Contract sheet, and a value of 1 in the corresponding cell in the As Built sheet, your code will return a +5 difference.
Cell A4 of the Generalized Report sheet will become 0

Is this what you want? I'd have thought you might want 10?

NWE
05-20-2019, 08:26 AM
Hi,

The 10 value is what I was aiming for, which is the absolute value of the wrong counts. Right now, my code has it calculating the +/- difference. I tried using the absolute function (which worked for percentages) but not for counts. Sorry for late reply i went camping over the weekend.

p45cal
05-20-2019, 10:58 AM
try:
Sub AudErrQuant2()
'This functions calculates error int and counts
With Worksheets("Generalized Report")
For x = 6 To 10001
If Sheets("Detail Report").Range("E" & x) = "Audit Error" Then
RowNo = Range(Sheets("Detail Report").Range("A" & x)).Row
.Range("A4").Value = .Range("A4").Value + Abs(Worksheets("Contract").Cells(RowNo, "G").Value - Worksheets("As Built").Cells(RowNo, "G").Value)
.Range("B4").Value = .Range("B4").Value + Abs(Worksheets("Contract").Cells(RowNo, "I").Value - Worksheets("As Built").Cells(RowNo, "I").Value)
.Range("C4").Value = .Range("C4").Value + Abs(Worksheets("Contract").Cells(RowNo, "J").Value - Worksheets("As Built").Cells(RowNo, "J").Value)
.Range("D4").Value = .Range("D4").Value + Abs(Worksheets("Contract").Cells(RowNo, "K").Value - Worksheets("As Built").Cells(RowNo, "K").Value)
End If
Next x
End With 'Worksheets("Generalized Report")
End Sub

NWE
05-20-2019, 12:08 PM
I see the difference in counts between mine and yours. Was my code calculating the +/- difference?

p45cal
05-20-2019, 12:19 PM
Was my code calculating the +/- difference?Yes.

NWE
05-20-2019, 12:24 PM
Ok then, lessons learned. Thank you for ALL of your help sir!