Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 25

Thread: Still Stuck On A loop

  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location

    Still Stuck On A loop

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    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.

  4. #4
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Hi NWE!
    There should be a better way. Please upload an attachment.


    --Okami

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by NWE View Post
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    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)
    Attached Files Attached Files

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    Sorry my mistake mate, here is the entire workbook. It is module 1, sub PrError() and subAurErrQuant() are giving me the issues.
    Attached Files Attached Files

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    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?

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    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!

  14. #14
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  15. #15
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    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.)

  16. #16
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  17. #17
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    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?

  18. #18
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  19. #19
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    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.

  20. #20
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •