Solved: Error with unmatched items

[lifeson]

Thought I had got this working until I looked closely at the data

The attached is a demo of part of a larger project

Click on the button and the form loads.

Click on the "List All" button and the form will display any materials that are linked to any pack (via sheet TblPack) from any packs listed on sheet TblQuoteLine - a material can be in any number of packs and can appear in the list more than once (eg MAT 184) - this works fine

Then click List Unique Items

The list created should be a list of the unique Mats but Some of the items that should appear are missing
eg MAT1147 only appears once and should be listed but is not there at all
I have highlighted the packs that are causing the problem
I am pretty sure the data is correct

This is the bit that is different between the two routines
[vba] If chk Is Nothing Then ' MatID does not already exist, add new record.
Set rng = shTarget.Cells(Rows.Count, 1).End(xlUp).Offset(1)
With rng
.Value = matID
.Offset(0, 1) = matDesc
.Offset(0, 2) = matPrice
.Offset(0, 3) = matTotal
.Offset(0, 4) = matTotal * matPrice
End With
Else 'find which row already contains matid
'MsgBox "Match for MATID" & matID
chkRow = WorksheetFunction.Match((matID), .Columns(1), 0)
MsgBox "MATID " & matID & "Is in row: " & chkRow
End If[/vba]

[Bob Phillips]

You know that the buttons on the form call te opposit actions, ListUnique calls ListAll?

Can you summarise what the code does in English, I personally don't have the patience to work through it.

[lifeson]

OK

The code is supposed to:

Look at each entityID on sheet TblQuoteLine

For each entityID:

Where entity is Type "2" (type 1 entity's do not have any materials linked and works fine)

look at Sheet TBlPackMat and find what MATID is linked to the entityID and qty of MATS

For each MATID linked, look at sheet NWAC and get the material description and price

Calculate how many MATS are required (entityQty ordered * matQty in pack)

Then add each record to sheet ?QuoteDetail?

The List All button - ?CommandButton2? will list all the materials regardless of whether they are duplicated or not

The ?List Unique Items? Button - cmdListAll_Click (cock up on the naming front ) has additional code that should check each record as it is added to ?QuoteDetail? to see if the MATID has already been added

[vba]Set chk = shTarget.Range("A:A").Find(matID, , , xlWhole) [/vba]

If the MATID has not already been added, then a new record on sheet ?QuoteDetail? is added
[vba]
If chk Is Nothing Then ' MatID does not already exist, add new record.
Set rng = shTarget.Cells(Rows.Count, 1).End(xlUp).Offset(1)
With rng
.Value = matID
.Offset(0, 1) = matDesc
.Offset(0, 2) = matPrice
.Offset(0, 3) = matTotal
.Offset(0, 4) = matTotal * matPrice
End With
[/vba]

If the MATID has already been added then I want to add the new qty to the existing MATID, but it is not listing the unique materials correctly
Some of the MATS that are linked are not appearing at all with this routine, but they do when I do not try to find matched records.

Hope that helps but probably still clear as mud

[lifeson]

Sorted it

It was the error handling that was causing the problem
This was the solution
[vba] If chk Is Nothing Then ' MatID does not already exist, add new record.
With rng
.Value = matID
.Offset(0, 1) = matDesc
.Offset(0, 2) = matPrice
.Offset(0, 3) = matTotal
.Offset(0, 4) = matTotal * matPrice
End With
Else 'find which row already contains matid
chkRow = WorksheetFunction.Match(matID, shTarget.Range("A:A"), 0)
'find the existing total
exTotal = shTarget.Cells(chkRow, "D").Value
'add new qty and existing qty
shTarget.Cells(chkRow, "D").Value = exTotal + matTotal

Set rng = shTarget.Cells(chkRow, "D")
With rng
.Value = exTotal + matTotal
End With
newTotal = rng.Value
With rng
.Offset(0, 1) = newTotal * matPrice
End With

End If[/vba]

Look at MAT184 as an example
When List All is selected MAT 184 is listed 4 times with qtys of 6,3,6,3
When list Unique is clicked, it appears once with a qty of 18

Maybe not the most elegant way, but it seems to work

[mdmackillop]

A way to improve your code is to remove reundancy. If you don't need Rng or NewTotal elsewhere. It makes it easier to follow and to debug.
[vba]
With shTarget.Cells(chkRow, "D")
.Value = exTotal + matTotal
.Offset(0, 1) = .Value * matPrice
End With

'is equivalent to

Set Rng = shTarget.Cells(chkRow, "D")
With Rng
.Value = exTotal + matTotal
End With
newTotal = Rng.Value
With Rng
.Offset(0, 1) = newTotal * matPrice
End With

[/vba]

[lifeson]

Thanks for the tip
I did think about trying that but thought it might cause a circular reference - obviously not though