lifeson
02-22-2008, 12:06 PM
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
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
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
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