I have three sheets: "bom", "MPS" and "DData". What I'm trying to do is first read the value of cell A2 from "MPS" and fetch all the rows from "bom" that in column A have that value and list them to "DData".
At the same time I'd need the values in column C and D from "MPS" to be fetched to the corresponding lines. So if the value in "MPS" cell A2 value matches 4 lines in "bom", the values from cells C2 and D2 should be put after those 4 lines. This doesn't properly work at the moment.
Once this loop is done it should move on to the cell value A3 in "MPS" and so on... The code below somewhat works. I've tried adding a second for-loop and everything else that came to mind but with no good luck. The biggest problem is that if MPS!A2
has value 1, A3
= 2 and A4
is 1 again, it doesn't list the values from "bom" a second time.
The code is based on this originally: https://stackoverflow.com/a/26912176
Public Sub CommandButton1_Click()
Dim countRows1 As Long, countRows2 As Long
countRows1 = 2 'the first row of your dataset in sheet1
endRows1 = 50 'the last row of your dataset in sheet1
countRows2 = 2 'the first row where you want to start writing the found rows
For j = countRows1 To endRows1
Dim keyword As String: keyword = Sheets("MPS").Cells("A2, A100").Value
If Sheets("bom").Range("A2, A100").Value = keyword Then
Sheets("DData").Rows(countRows2).Value = Sheets("bom").Rows(j).Value
Sheets("DData").Rows(countRows2).Cells(6).Value = Sheets("MPS").Rows(countRows2).Cells(3).Value
Sheets("DData").Rows(countRows2).Cells(7).Value = Sheets("MPS").Rows(countRows2).Cells(4).Value
countRows2 = countRows2 + 1
End If
Next j
End Sub
My mind says that this definitely needs two loops but I just couldn't get it working.
I can't post images but I'll try to illustrate what is needed and happening better below.
Sheet "bom" structure and data (range A1:E7):
id desc id_part desc_part qty
30010 build1 10200 part1 1
30010 build1 23002 part2 3
30010 build1 21003 part3 500
30010 build1 21503 part4 400
20010 build2 10210 part5 100
20010 build2 10001 part6 5
Sheet "MPS" structure and data (range A1:D4):
id desc week batches
30010 build1 1 2
20010 build2 2 4
30010 build1 2 0
Sheet "DData" structure (range A1:H3) and what is returned with your code panhandel:
id desc id_part desc_part qty week batches total(=qty*batches)
30010 1 2
30010 2 0
And my goal is this:
id desc id_part desc_part qty week batches total (=qty*batches)
30010 build1 10200 part1 1 1 2
30010 build1 23002 part2 3 1 2
30010 build1 21003 part3 500 1 2
30010 build1 21503 part4 400 1 2
20010 build2 10210 part5 100 2 4
20010 build2 10001 part6 5 2 4
30010 build1 10200 part1 1 2 0
30010 build1 23002 part2 3 2 0
30010 build1 21003 part3 500 2 0
30010 build1 21503 part4 400 2 0
... also where H2 for example would have the value of E2 * G2.
*** I tried to change
Sheets("DData").Range("A" & countRows2).Value = Sheets("bom").Range("A" & lCount).Value
to
Sheets("DData").Rows(countRows2).Value = Sheets("bom").Rows(lCount).Value
for example, but Excel started crashing hard. Is it wiser to use Range instead of Rows?