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:

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.

Hi NWE!
The code below is written incorrectly, but the runtime will prompt errors,
So it's not the cause of your problem.

Code:

---

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.

Code:

---

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

---

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?

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

Well I changed the code around and it works accept that the answer disappears out of the target cell after it runs.

Code:

---

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

---

Quote:

---

and it works accept that the answer disappears out of the target cell after it runs.

---

That's an unusual definition of 'works'

Quote:

---

"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

Quote:

---

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

Code:

---

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,

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!

So I took your loop and I am trying to expand upon it, but my math is not right.

Code:

---

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).R 
    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..

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.

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.