Still Stuck On A loop

[NWE]

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]

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]

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]

Hi NWE!
There should be a better way. Please upload an attachment.


--Okami

[Bob Phillips]

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]

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]

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]

Sorry my mistake mate, here is the entire workbook. It is module 1, sub PrError() and subAurErrQuant() are giving me the issues.

[Bob Phillips]

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]

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]

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]

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,A25:D25,A27,D22") = 0
  .Range("A28") = ""
End With
End Sub

[NWE]

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]

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]

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]

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]

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]

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]

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]

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?