PDA

View Full Version : Isolating errors 2



NWE
04-29-2019, 02:59 PM
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.

大灰狼1976
04-29-2019, 06:23 PM
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

NWE
05-01-2019, 08:06 AM
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?

Paul_Hossler
05-01-2019, 11:49 AM
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

NWE
05-01-2019, 11:54 AM
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

Paul_Hossler
05-01-2019, 02:17 PM
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

NWE
05-01-2019, 02:33 PM
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!

NWE
05-02-2019, 10:55 AM
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..

Paul_Hossler
05-03-2019, 09:05 AM
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.

NWE
05-06-2019, 08:59 AM
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.