PDA

View Full Version : [SOLVED] Produce parts list from breakdown



Pimpleot
12-17-2019, 09:04 AM
Hi I'm very new to vba and need help in making a list populate/calculate from a much larger list containing multiple usages of the same descriptions.

In the attached file I would like 'Parts Order Form' tab to auto-populate the 'Item Description' and 'QTY' columns from the 'Project Mgmt Final' Tab 'Item Description' and 'Quantity' columns, but also only have one of each description and a total quantity.

Thank you in advance.

paulked
12-18-2019, 06:29 AM
Hi and welcome to the forum.

Not very elegant, but it works!



Sub Update()
Dim i As Long, r As Long
Application.ScreenUpdating = False
r = 16
With Sheet2
For i = 2 To Sheet1.Cells(Rows.Count, 17).End(xlUp).Row
.Cells(r, 5) = Sheet1.Cells(i, 17)
.Cells(r, 4) = Sheet1.Cells(i, 19)
r = r + 1
Next
r = 16
Do Until .Cells(r, 5) = ""
For i = 16 To .Cells(Rows.Count, 5).End(xlUp).Row
If i = r Then GoTo Nxt
If .Cells(i, 5) = .Cells(r, 5) Then
.Cells(r, 4) = .Cells(r, 4) + .Cells(i, 4)
Range(.Cells(i, 5), .Cells(i, 4)).Delete
End If
Nxt:
Next
r = r + 1
Loop
End With
End Sub

Pimpleot
12-18-2019, 09:50 AM
Thanks very much for doing this. When I run it in my version it gets stuck at End If according to the debugger.

paulked
12-18-2019, 12:15 PM
With what error? Post your workbook :thumb

Pimpleot
12-18-2019, 01:10 PM
Not able to upload full workbook as too large even compressed. But have given basic format of it. I'm sure it's a kindie error on my part but I'm very appreciative of your time.

paulked
12-18-2019, 03:07 PM
I'm not at my PC at the mo, but you need to change sheet1 and sheet2 to match the changed sheet code names and vba can't count #REF!... Put some real numbers in there ��