엑셀VBA기초 : formula를 활용한 수식삽입(address, formula, offset, k=i, ",""문자"",")
1.개념
1) .formula는 Workbook().Range(영역).formula = "=sumif()" 처럼 VBA를 통해 특정 영역에 수식을 삽입할 때 사용.
2) 주의할 점은 엑셀시트의 수식에 VBA의 영역값은 인식되지 않으므로 문자를 칭하는 "(쌍따옴표)를 주의
-> 따라서, formula를 사용할 때는 "(쌍따옴표)처리가 까다롭다.
2. 형식 및 예시
1) 단순화 하면, Worksheets.Range.Formula = "=sumif(영역, 조건, 합계영역)" 이다.
2) 조건반복문의 내용을 뜯어보면,
(1) 입력_WS.Range("G" & i).Offset(0,j).Formula <- 입력시트의 조건에 맞는 G열의 행에 수식을 입력. (j는 column)
(2) "=sumif(" & 입력_WS.Range(입력_WS.Cells(2,6). Offset(k,0), 입력_WS.Cells(i,7).Offset(0,j)).Address <- 단순화하면
"=sumif(" & 시트의 영역 이다. Address를 사용하는 이유는, Range(Cells(),Cells())의 범위를 주소값으로 반환해서
시트에 엑셀수식이 인식할 수 있는 표현을 위한 것.
(3) &",""받을""," <-sumif의 '조건값' 이다. 주의할 점은 보통 VBA의 문자값 사이에는 &(and)연산자가 들어가야
하지만, formula를 통한 수식입력에서 변수와 영역이 아닌 단순'문자값'은 &(and)연산자가 들어가지 않는다.
(4) & 입력_WS.Range(입력_WS.Cells(2,7).Offset(k,j), 입력_WS.Cells(i-1, 7).Offset(0, j)).Address & ")" <-
Sumif의 '합계범위'이다. 범위값이므로 Range(Cells(),Cells().address로 영역의 주소값을 반환해준다.
3) formula식을 다르게 표현하면,
(1) 입력_WS.Range("G" & i).Offset(0, j).Formula = "=sumif(" & 입력_WS.Cells(끝값, 6)
.Address(False, False) & ":" & 입력_WS.Cells(i, 입력_LC).Address(False, False) & "," &
입력_WS.Cells(i, 6).Address(False, False) & "," & 입력_WS.Cells(끝값, 7 + j).Address(False, False) & ":" &
입력_WS.Cells(i, 7 + j).Address(False, False) & ")"
(2) 이처럼 영역값을 .address & ":" & .address로 설정해도 된다.