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

엑셀(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