엑셀(EXCEL) – 동적이름정의-VLOOKUP(그림참조하는 법)-주문서 작성

 
http://www.clien.net/cs2/bbs/board.php?bo_table=kin&wr_id=3480070

팁란에 글을 올릴 재료가 없어서 심심하던 차에 아질게에 위의 질문이 올라왔습니다.

“아 제품 주문서를 엑셀로 만들려고 하는데 Asheet가 통합 주문서면 그 주문서에서 품목별 사진을
Bsheet ,Csheet 이런식으로 장당 그림을 넣어놧거든요 근데 보기도 안좋고 해서 그림넣어둔 sheet를
숨기거나 하고싶어서요”

어떻게 풀어야할지 고민을 좀 해봤습니다. 그리고 구걸신에에 또 구걸을 좀 해 봅니다.
사실 아이디어를 찾으면 금방 해결될 문제이고 그 방법을 현실에 맞게 바꾸어 주면 됩니다.

주문을 하다 보면 가끔 각 제품의 주문을 잘못 내는 경우가 생깁니다. 그래서 그 제품을 선택하면
제품 사진을 보여주어 명확히 하면 제품 주문의 오류를 막아줄 수 있습니다. 위의 첨부 그림과 같이
제품을 선택하면 제일 오른쪽 칼럼에 사진을 보여 주도록 VBA를 작성할 예정입니다.

첫번째로 제가 올리는 팁에서 자주 사용하는 동적이름 정의를 이용할 예정입니다.
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,4)
위의 내용을 설명해 보면 Sheet2의 A2에서 아래로 셀의 내용이 있는 부분과 오른쪽으로 4칼럼을
자동적으로 취하여 PicTable라는 이름의 내용으로 변경합니다.

두번째로 VLOOKUP 함수를 사용, 동적이름정의로 정의된 부분에서 A열의 이름과 같은 그림의
이름을 취해서 그 그림의 이름과 같은 그림을 VBA함수를 이용하여 나머지 그림은 숨기고 같은
이름의 그림만 보여줍니다.
=VLOOKUP(A2, PicTable, 2, FALSE)

세번째 VBA함수 입니다. 위의 설명대로 과일그림을 과일 이름과 비교하여 같은 것만 보여줍니다.

Private Sub Worksheet_Calculate()

Dim ObjPic As Picture

‘시트의 모든 그림을 숨깁니다.
Me.Pictures.Visible = False

‘현재 셀에서 오른쪽으로 6째 칼럼의 이름과 같은 그림이름을 비교하여 보여줍니다.
With ActiveCell.Offset(0, 6)

For Each ObjPic In Me.Pictures

If ObjPic.Name = .Text Then

ObjPic.Visible = True
ObjPic.Top = .Top
ObjPic.Left = .Left

ObjPic.ShapeRange.LockAspectRatio = msoFalse
ObjPic.Placement = xlMoveAndSize
ObjPic.ShapeRange.Width = .Width
ObjPic.ShapeRange.Height = .Height * 8

‘같은 그림이름을 발견하면 루프를 빠져나갑니다.
Exit For

End If

Next ObjPic

End With

End Sub

그냥 예제로 간단하게 오리는 것이므로 첨부된 화일에서 양식 및 에러처리는 하지 않았습니다.
첨부화일을 보시면서 조금씩 공부하고 해석하시면 멋진 주문서를 만들어 사용하실 수 있을 것으로
판단되어(사실 귀차니즘) 그냥 올려 드립니다.

첨부 : 20150717-동적이름정의-VLOOKUP(그림참조)-주문서