엑셀(Excel) VBA – 다른 시트의 값을 가져와서 데이터를 추가하는 방법

 

환경: Microsoft Excel 2010

 

오늘 소개할 내용은 VBA 를 이용해서 다른 시트에 있는 값을 가져와 복사하는 방법입니다. 예제는 Sheet2 에 원가표가 있고 Sheet1 에 판매 데이터가 있습니다. 이익금이 얼마인지 계산하기 위해 Sheet2 에 있는 판매한 제품의 원가를 가져와서 Sheet1 에 있는 복사하는 것입니다. 

 

▼ 샘플은 Sheet2 에 제품원가가 있으며,  Sheet1 에 판매한 제품 리스트가 있습니다. Sheet2 에 해당 제품 원가를 가져와 Sheet1 의 동일한 제품명의 원가 항목에 넣을 예정입니다. 계산 로직은 Sheet2 에 제목을 제외한 원가영역을 가져온 뒤 Shee1 에 있는 데이터 행 수만큼 For 문을 돌려서 데이터를 찾습니다. 동일한 데이터가 있다면 원가만 추출한 후 Sheet1 에 셋팅 하는 방식입니다.  

 

▼ 먼저 Sheet2 에 있는 제품 원가표 영역을 가져오는 소스를 알려 드리겠습니다. Worksheets(2).UsedRange 2번째 워크시트에서 사용하고 있는 데이터 전체 영역을 가리키고 있습니다. 제목에 해당하는 부분을 제외하기 위해 offset(1) 으로 한칸 이동한뒤 전체 영역을 Range 변수에 담았습니다.

 

With Worksheets(2).UsedRange

   Set rngList = .Offset(1).Resize(.Rows.Count - 1)

End With

 

Sheet1 에 값을 넣기 위해서는 얼마나 많은 행이 있는지 알아야 합니다. 그래서 WorksheetFunction.CountA(Range("A:A")) 함수를 이용해서 A 열의 총 개수를 알아 옵니다. 그런데 행의 개수에서 -1 을 한 것은 제목 다음부터 시작하기 위해서 입니다. i=0 이 아닌 1 부터 시작했기 때문에 총 개수에서 하나를 줄여야 합니다. 

 

For i = 1 To WorksheetFunction.CountA(Range("A:A")) - 1

Next i

 

▼ 이제 For 문 안에 있는 소스를 보겠습니다. 원가표에서 제품코드를 찾아야 합니다. Find 함수를 이용해서 제품코드에 해당하는 값이 있는지 찾아서 행 전체를 리턴합니다. 전체 행 중에서 필요한 것은 원가입니다. 그래서 rngValue.End(2) 한 것입니다.

 

Set rngValue = rngList.Find(Range("A1").Offset(i, 0))

If Not rngValue Is Nothing Then

   strPrice = rngValue.End(2)

End If

 

▼ 이렇게 제품코드에 해당하는 원가를 가져온 뒤 Sheet1 에 값을 셋팅합니다. Offset 함수를 이용해서 C 열에 차례대로 넣습니다. 

 

Range("C1").Offset(i, 0) = strPrice

 

▼ 아래는 위에서 설명한 전체 과정에 대한 완성된 소스 입니다. 버튼과 연결해서 사용하시면 되겠죠.

 

Sub Sheet_Click()

    Dim rngList As Range

    Dim rngValue As Range

    Dim strPrice As String

    Dim i As Integer

   

    '' Sheet2 에 있는 데이터 영역만 추려가 가져옵니다. .

    With Worksheets(2).UsedRange

        Set rngList = .Offset(1).Resize(.Rows.Count - 1)

    End With

   

    ''A열 전체 수를 구한 후에 헤더부분을 제외하기 위해 1 빼줌

    For i = 1 To WorksheetFunction.CountA(Range("A:A")) - 1

        Set rngValue = rngList.Find(Range("A1").Offset(i, 0))

 

        If Not rngValue Is Nothing Then

            strPrice = rngValue.End(2)

        End If

 

        Range("C1").Offset(i, 0) = strPrice

 

    Next i

End Sub

 

Posted by 녹두장군