Consulting

Results 1 to 10 of 10

Thread: Isolating errors 2

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

    Post Isolating errors 2

    I am stuck on a piece of code that I am tweaking.

    "Detail Report" has two columns: Column C is Pr Code and Column B is the line item associated with the code. Both of these come from comparing column L on two other worksheets. I would like to isolate the line items with text value "Pr Code" and then add up a quantity associated with it that comes from the second of the two compared sheets. I have tried an array and it gave me the wrong result, so I am trying to figure out what is wrong with this code:

    Sub PrNonMatch()
    Dim rng As Range
    Dim c As Variant
    Set rng = Worksheets("Detail Report").Range("C6:C10006")
    
    
        For Each c In rng
            If c.Vale = "Pr Code" Then
            Worksheets("Generalized Report").Range("E8") = Evaluate("=SUMPRODUCT(--('As Built'!I2:I10000))")
            Else
            'There are no differences in Pr code
            Worksheets("Generalized Report").Range("E8") = "0"
            End If
        Next c
    End Sub
    I should get a value of 5, but I am getting zero.

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Hi NWE!
    The code below is written incorrectly, but the runtime will prompt errors,
    So it's not the cause of your problem.
    If c.Vale = "Pr Code" Then
    For each...next loops get the result of the last loops if they do not jump out of the condition.
    Sub PrNonMatch()
      Dim rng As Range
      Dim c As Variant
      Set rng = Worksheets("Detail Report").Range("C6:C10006").Find("Pr Code", lookat:=xlWhole)
      With Worksheets("Generalized Report")
        If rng Is Nothing Then
          .[e8] = "0"
        Else
          .[e8] = Evaluate("=SUMPRODUCT(--('As Built'!I2:I10000))")
        End If
      End With
    End Sub
    Last edited by 大灰狼1976; 04-29-2019 at 06:35 PM.

  3. #3
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    Hi,

    So the code above still gives me the same issue. It is calculating the entire range, not just the cells that have the "Pr Code" in them. Any thoughts?

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I think it'd be helpful if you could attach a small sample workbook with enough data that shows the issue, along with what you think the correct answer is
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    Well I changed the code around and it works accept that the answer disappears out of the target cell after it runs.
    Sub PrNonMatch()
    For x = 6 To 10000
    If Sheets("Detail Report").Range("E" & x) = "" Then
        decnt = x
        x = 10001
    End If
    Next x
    
    
    For x = 2 To 10000
    If Sheets("As Built").Range("L" & x) = "" Then
        bltcnt = x
        x = 10001
    End If
    Next x
    
    
    For x = 6 To decnt
        For y = 2 To bltcnt
            If Sheets("Detail Report").Range("E" & x) = Sheets("As Built").Range("L" & y) Then
               Worksheets("Generalized Report").Range("E8") = (Sheets("As Built").Range("I" & y))
            End If
        Next y
    Next x
    End Sub

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    and it works accept that the answer disappears out of the target cell after it runs.
    That's an unusual definition of 'works'


    "Detail Report" has two columns: Column C is Pr Code and Column B is the line item associated with the code. Both of these come from comparing column L on two other worksheets. I would like to isolate the line items with text value "Pr Code" and ….
    Your latest macro doesn't use Col B or C


    then add up a quantity associated with it that comes from the second of the two compared sheets.


    1. Your macro doesn't do any 'adding up'

    2. You just keep overwriting cell E8 on Generalized Report


    Since I have no data to test, this is just a guess

    Option Explicit
    
    '"Detail Report" has two columns: Column C is Pr Code and Column B is the line item associated with the code.
    'Both of these come from comparing column L on two other worksheets. I would like to isolate the line items with text value "Pr Code" and then
    'add up a quantity associated with it that comes from the second of the two compared sheets.
    
    Sub PrNonMatch()
        Dim wsGR As Worksheet, wsAB As Worksheet, wsDR As Worksheet
        Dim x As Long, y As Long, decnt As Long, blcnt As Long
        
        Set wsGR = Worksheets("Generalized Report")
        Set wsAB = Worksheets("As Built")
        Set wsDR = Worksheets("Detail Report")
        
        decnt = wsDR.Cells(6, 5).End(xlDown).Row
        blcnt = wsAB.Cells(2, 12).End(xlDown).Row
    
        For x = 6 To decnt
            For y = 2 To blcnt
                If wsDR.Cells(x, 5).Value = wsAB.Cells(y, 12) Then
                   wsGR.Range("E8") = wsGR.Range("E8") + wsAB.Cells(y, 9).Value
                End If
            Next y
        Next x
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    Paul,

    Looking at your code, I actually figured out what was wrong with mine. I needed to add the range to the value so that the code would work. Like you said I was just overwriting cell E8. At this point, either my code with the adjustment or your code works fine.

    The range changed because my logic from the first request was wrong. The second code represents the right logic. Thanks for the assist sir!

  8. #8
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    So I took your loop and I am trying to expand upon it, but my math is not right.
    Sub Auder()
    
    
     Dim wsGR As Worksheet, wsAB As Worksheet, wsDR As Worksheet
        Dim x As Long, y As Long, decnt As Long, blcnt As Long
        
        Set wsGR = Worksheets("Generalized Report")
        Set wsAB = Worksheets("As Built")
        Set wsDR = Worksheets("Detail Report")
        Set wsCO = Worksheets("Contract")
        
        decnt = wsDR.Cells(6, 5).End(xlDown).Row
        blcnt = wsAB.Cells(2, 23).End(xlDown).Row
    
    
        
        For x = 6 To decnt
            For y = 2 To blcnt
            If wsDR.Cells(x, 5).Value = wsAB.Cells(y, 23) Then
                    If wsAB.Cells(y, 23) = "Audit Error" Then
                    wsGR.Range("A4") = wsGR.Range("A4") + wsCO.Cells(y, 7) - wsAB.Cells(y, 7)
                    wsGR.Range("B4") = wsGR.Range("B4") + wsCO.Cells(y, 9) - wsAB.Cells(y, 9)
                    wsGR.Range("C4") = wsGR.Range("C4") + wsCO.Cells(y, 10) - wsAB.Cells(y, 10)
                    wsGR.Range("D4") = wsGR.Range("D4") + wsCO.Cells(y, 11) - wsAB.Cells(y, 11)
                    wsGR.Range("E4") = wsGR.Range("E4") + wsCO.Cells(y, 15) - wsAB.Cells(y, 15)
                    End If
            End If
        Next y
    Next x
    End Sub
    A4 should equal 7
    and B4 should equal 6.

    the only thing I did different was add a text identifier to it..

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Without having a sample workbook with data that shows the error, I don't have any idea since it seems like it depends on the data in (e.g.) wsAB.Cells(y,7), etc.
    Last edited by Paul_Hossler; 05-04-2019 at 07:45 AM. Reason: Finish the sentence
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    VBAX Regular
    Joined
    Nov 2018
    Posts
    75
    Location
    Well after playing around with it some more, I was making it to complicated. For the non match functions, I used a string occurrence counting function on the report page. It was simpler than trying to isolate based on a certain "", and then count.

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
  •