☰

Excel VBA 행 가져오기 루프

tag-icon • tag-icon microsoft-excel vba
Excel VBA 행 가져오기 루프

"bom", "MPS", "DData"라는 세 개의 시트가 있습니다. 내가 하려는 일은 먼저 "MPS"에서 셀 A2의 값을 읽고 열 A에 해당 값이 있는 "bom"의 모든 행을 가져와서 "DData"에 나열하는 것입니다.

동시에 해당 줄로 가져오려면 "MPS"의 C 열과 D 열의 값이 필요합니다. 따라서 "MPS" 셀 A2 값의 값이 "bom"의 4줄과 일치하는 경우 셀 C2 및 D2의 값은 해당 4줄 뒤에 배치되어야 합니다. 현재로서는 제대로 작동하지 않습니다.

이 루프가 완료되면 "MPS"의 셀 값 A3으로 이동해야 합니다. 아래 코드는 어느 정도 작동합니다. 나는 두 번째 for-loop와 마음에 떠오른 모든 것을 추가하려고 시도했지만 행운이 없었습니다. 가장 큰 문제는 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이 심하게 충돌하기 시작했습니다. 행 대신 범위를 사용하는 것이 더 현명한가요?

답변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

관련 정보