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


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?

  • You've lost me on your second paragraph. Can you rephrase or clear up what 4 lines you're speaking of in this bit? "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" – panhandel May 23 '15 at 18:02
  • Sorry! I've tried to explain it on my answer below (week & batches) – Ben Gretzky May 23 '15 at 22:25
  • The only place I've seen .Rows used is when copying and pasting. I'm not sure you can follow it with a .Value. And thanks for the clarification. You will need two loops; one to go through MPS rows, and for each of those rows, another loop to scan through all bom rows. I'll mod my answer soon. – panhandel May 24 '15 at 4:21

EDITED: One loop goes line by line through MPS column A, the second loop compares each MPS column A value to all of the "bom" column A values. Once a match is found, each individual cell is copied (there's a faster way to do this I'm sure, but this illustrates what's going on well) to the DData sheet, and column H gets a formula to figure your total.

The tabs are set the same as what you have now, and results in what you are expecting/needing.

Sub Button1_Click()
    Dim countRows2 As Long
    countRows2 = 2 'the first row where you want to start writing the found rows

    Dim szMPSValues As Variant
    Dim szbomValues As Variant
    Dim lCount As Long
    Dim lCountbom As Long
    Dim MPSRng As Range
    Dim bomRng As Range
    Dim szConcatString As Variant
    Dim strKeyword As String

    'gets range of used cells
    Set MPSRng = Intersect(Columns("A").Cells, Worksheets("MPS").UsedRange)
    If MPSRng Is Nothing Then MsgBox "Nothing to do"

    'have to switch sheets to set the second loop's range of "bom" values
    Set bomRng = Intersect(Columns("A").Cells, Worksheets("bom").UsedRange)

    'saves range values into arrays
    szMPSValues = MPSRng.Value
    szbomValues = bomRng.Value

    'double check a to be sure its an array and of proper size
    If Not IsArray(szMPSValues) Then ReDim a(1, 1): szMPSValues = MPSRng.Value

    'loop through array concatenating cell values with a space after cell value
    'NOTE: Changed this to start at 2 in case you have a header row**
    For lCount = 2 To UBound(szMPSValues)
        strKeyword = Sheets("MPS").Range("A" & lCount).Value            'gets MPS.A2, MPS.A3, etc

        For lCountbom = 2 To UBound(szbomValues)
            If Sheets("bom").Range("A" & lCountbom).Value = strKeyword Then    'compares to bom.A2, bom.A3, etc

                    Sheets("DData").Range("A" & countRows2).Value = Sheets("bom").Range("A" & lCountbom).Value
                    Sheets("DData").Range("B" & countRows2).Value = Sheets("bom").Range("B" & lCountbom).Value
                    Sheets("DData").Range("C" & countRows2).Value = Sheets("bom").Range("C" & lCountbom).Value
                    Sheets("DData").Range("D" & countRows2).Value = Sheets("bom").Range("D" & lCountbom).Value
                    Sheets("DData").Range("E" & countRows2).Value = Sheets("bom").Range("E" & lCountbom).Value
                    Sheets("DData").Range("F" & countRows2).Value = Sheets("MPS").Range("C" & lCount).Value
                    Sheets("DData").Range("G" & countRows2).Value = Sheets("MPS").Range("D" & lCount).Value
                    Sheets("DData").Range("H" & countRows2).Formula = "=$F" & countRows2 & "*$G" & countRows2
                    countRows2 = countRows2 + 1
            End If
        Next lCountbom
    Next lCount
End Sub
  • Thank you for the input! I meant to just answer but commented instead.. – Ben Gretzky May 23 '15 at 21:29
  • I messed up with the format of superuser and edited the original question to include new data. – Ben Gretzky May 23 '15 at 23:33
  • Updated; this gets you the DData sheet just like you want now. – panhandel May 24 '15 at 4:54
  • Yes it does, thank you! I got the error Run-time error '1004': Methor 'Intersect' of object '_Global' failed from the line Set MPSRng = Intersect(Columns("A").Cells, Worksheets("MPS").UsedRange) and Set bomRng = Intersect(Columns("A").Cells, Worksheets("bom").UsedRange) but corrected them by writing them to Set MPSRng = Intersect(Worksheets("MPS").Columns("A").Cells, Worksheets("MPS").UsedRange) and Set bomRng = Intersect(Worksheets("bom").Columns("A").Cells, Worksheets("bom").UsedRange) - is this the correct way? – Ben Gretzky May 24 '15 at 10:48
  • If it works for you, I say run with it! – panhandel May 24 '15 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.