☰

Цикл выборки строк Excel VBA

tag-icon • tag-icon microsoft-excel vba
Цикл выборки строк Excel VBA

У меня есть три листа: "bom", "MPS" и "DData". Я пытаюсь сначала прочитать значение ячейки A2 из "MPS" и извлечь все строки из "bom", которые в столбце A имеют это значение, и перечислить их в "DData".

В то же время мне нужно, чтобы значения в столбцах C и D из "MPS" были извлечены в соответствующие строки. Так что если значение в ячейке A2 "MPS" совпадает со значением 4 строк в "bom", значения из ячеек C2 и D2 должны быть помещены после этих 4 строк. В данный момент это не работает должным образом.

После завершения этого цикла он должен перейти к значению ячейки A3 в "MPS" и так далее... Код ниже в некоторой степени работает. Я пробовал добавлять второй цикл for и все остальное, что приходило в голову, но безуспешно. Самая большая проблема в том, что если MPS!A2имеет значение 1, A3= 2 и A4снова равно 1, он не перечисляет значения из "bom" во второй раз.

Первоначально код основан на этом: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

Мой разум говорит, что здесь определенно нужны два цикла, но у меня просто не получается это сделать.

Я не могу публиковать изображения, но постараюсь ниже лучше проиллюстрировать, что необходимо и что происходит.

Структура и данные листа «bom» (диапазон 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

Структура и данные листа «MPS» (диапазон A1:D4):

 id     desc    week    batches
30010   build1  1         2
20010   build2  2         4
30010   build1  2         0

Структура листа «DData» (диапазон A1:H3) и то, что возвращается с помощью вашего кода panhandel:

id      desc    id_part    desc_part    qty     week     batches    total(=qty*batches)
30010                                             1          2  
30010                                             2          0

И моя цель такова:

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

... также где H2, например, будет иметь значение E2 * G2.

*** Я пытался изменить

Sheets("DData").Range("A" & countRows2).Value = Sheets("bom").Range("A" & lCount).Value

к

Sheets("DData").Rows(countRows2).Value = Sheets("bom").Rows(lCount).Value

например, но Excel начал сильно зависать. Разумнее ли использовать Range вместо Rows?

решение1

ОТРЕДАКТИРОВАНО: Один цикл проходит построчно через столбец MPS A, второй цикл сравнивает каждое значение столбца MPS A со всеми значениями столбца "bom" A. Как только совпадение найдено, каждая отдельная ячейка копируется (я уверен, что есть более быстрый способ сделать это, но этот хорошо иллюстрирует происходящее) на лист DData, а столбец H получает формулу для расчета общей суммы.

Вкладки устанавливаются так же, как и сейчас, и в результате получается то, что вы ожидаете/требуете.

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
    Worksheets("bom").Activate
    Set bomRng = Intersect(Columns("A").Cells, Worksheets("bom").UsedRange)
    Worksheets("MPS").Activate

    '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

Связанный контент