엑셀과 VBA의 Offset 함수가 다르다. (3)

(3) 떨어진 셀부터 일정 영역 지정하기

엑셀에서는 Offset 함수에 height와 width를 지정할 수 있는 인수가 있지만,

VBA에는 없으므로 Resize 속성(Property)을 이용해야 합니다.

 

(가) 구문

expression.Resize (RowSize, ColumnSize)입니다.

여기서 expression에는 범위(셀)가 올 수 있으며

RowSize와 ColumnSize로 높이와 너비를 지정하는 것입니다.

 

Offset 함수와 구별해야 할 것은 

RowOffset이나 ColumnOffset의 경우는 떨어진 거리이므로 0부터 시작하는데,

RowSize와 ColumnSize는 크기이므로 1부터 시작한다는 것입니다.

 

(나) 일정 영역 지정하기

offset 비교(완성2).xlsm

0.01MB

 

아래와 같이 코드를 작성하고 실행하면

Sub resize1()
    Range("c9").Resize(3, 1).Select
End Sub

 

C9셀부터 아래로 3칸, 오른쪽으로 한 칸인 범위가 선택됩니다.

Select가 선택하라는 명령입니다.

 

(다) 일정 영역의 합계 구하기

Sub resize2()
    Range("a2") = "=Sum(" & Range("c9").Resize(3, 1).Address(0, 0) & ")"
End Sub

 

 

(4) Offset과 Match 함수 결합하기

이런 경우는 엑셀 함수로 처리하는 것이 편리하지만

VBA로도 필요한 경우가 있을 것이므로 

 

H2셀의 수식

=OFFSET(G7,MATCH(G2,G8:G9,0),MATCH(H1,H7:J7,0))을 

 

VBA로 표현하는 세 가지 방법을 알아보겠습니다.

 

(가) 수식 전체를 문자처럼 큰따옴표 안에 넣는 방법

Sub offset_match1()
    Range("H2") = "=OFFSET(G7,MATCH(G2,G8:G9,0),MATCH(H1,H7:J7,0))"
End Sub

 

엑셀로 돌아가서 H2셀을 지운 다음

개발도구 - 매크로 명령을 누른 다음

매크로 이름 목록에서 'offset_match1'을 클릭하고, 실행 버튼을 누르면 위 화면과 같이 수식이 입력됩니다.

 

입력될 셀 주소 표시는 다르지만 그 다음 수식은 엑셀에서 입력하는 것과 같습니다.

 

위 매크로를 실행하면 수식이 아니라 값으로 입력됩니다.

 

 

(나) 값을 수식으로 바꾸는 방법

값이 아니라 수식 자체로 입력하려면 셀 주소 표현식을 이용해야 하는데 다르게 표현해 보겠습니다.

Sub offset_match3()
    Range("H2") = "=offset(" & [G7].Address(0, 0) _
                    & ",match(" & [g2].Address(0, 0) & "," & [G8:G9].Address(0, 0) & ",0)" _
                    & ",match(" & [h1].Address(0, 0) & "," & [h7:j7].Address(0, 0) & ",0))"
End Sub

 

Range("g7")을 [g7]이라고 표시할 수 있습니다.

따라서, Range("g7").address(0,0)은 [g7].address(0,0)이 됩니다.

 

이렇게 만든 것이 위 매크로입니다.

 

이제 이 매크로를 실행하면 수식으로 잘 입력되었습니다.

 

offset 비교(완성3).xlsm

0.02MB