Loop problem

[lifeson]

I am having trouble with this part of code which is part of a larger project

My loop doesn't work :wot

It should lookat each row from sheet TblQuoteLine and then for any entity type 2's use the entity ID as a lookup key on sheet TblPackMat and return any matching MATID' to sheet QuoteDetail

I have sorted out adding entity type 1's apart from checking if there are any duplicates

The other issue is going to be totalling all the Mats ordered.
each pack can contain many mats of any quantity but a pack could alos be ordered more than once!

e.g

Pack 100 may contain:
MATID 1 qty = 1
MATID 2 qty = 1
MATID 3 qty = 12

pack 200 may contain
MATID 1 qty 2
pack 200 is added twice to the quote

therfore the final outcome should be

MATID 1 total qty = 5 (1 from pack 100, 4 from pack 200)
MATID 2 total qty = 1
MATID 3 total qty = 12

I've seperated the two stages - adding type 1 and adding type 2 making it easier for me to understand

[Bob Phillips]

Private Sub cmdType2_Click()
Dim entityID As String
Dim r As Long
Dim i As Long
Dim iType As Long
Dim srcRow As Long 'number of rows on QuoteLine to check
Dim packRow As Long ' number of rows on sheet PackMat
Dim priceRow As Long ' number of rows on sheet Price
Dim matID As String
Dim shTarget As Worksheet
Dim shPrice As Worksheet
Dim shMat As Worksheet

Application.ScreenUpdating = False

Set shTarget = Worksheets("QuoteDetail")
Set shPrice = Worksheets("TblPrice")
Set shMat = Worksheets("TblPackMat")

With Sheets("TblQuoteLine")

srcRow = .Cells(.Rows.Count, "A").End(xlUp).row
packRow = shMat.Cells(shMat.Rows.Count, "A").End(xlUp).row - 1
priceRow = shPrice.Cells(shPrice.Rows.Count, "A").End(xlUp).row - 1

For r = 2 To srcRow

'declare variables
entityID = .Cells(r, "A").Value
iType = .Cells(r, "B").Value
compQty = .Cells(r, "C").Value

If iType = 2 Then 'get linked materials

'loop through packmat links to find mats
For i = 2 To packRow

'MsgBox "For entity: " & entityID & " check row: " & i & " of: " & packRow
If shMat.Cells(i, "A").Value = entityID Then

matID = shMat.Cells(i, "B").Value
'add MATID to list
shTarget.Cells(shTarget.Rows.Count, 1).End(xlUp).Offset(1) = matID
End If
Next i
End If
Next r

.Activate
Application.ScreenUpdating = True
End With

End Sub

[lifeson]

Thanks XLD
Thtas worked fine and fixed the loop problem, all the materials get added (and its tidied up my code as well :whistle: )

It hasn't fixed my problem of adding the quantity if the materials has already been added.
I'll have a play and see what mess I can come up with :rotlaugh: