소재지, 특지구분, 본번, 부번 합치기(2) - VBA
1. 문제
소재지와 특지구분, 본번, 부번이 나뉘어져 있을 경우에 이를 합치는 것을 VBA로 해결해보겠습니다.
두 가지 경우가 있는데,
첫번째는 특지구분에 산과 빈칸이 있고, 부번이 숫자와 빈칸인 경우이고,
두번째는 특지구분에 산과 일반이 있고, 부번이 0과 양수로 구성된 경우입니다.
2. 특지 구분에 일반이 없는 경우
개발도구 - Visual Basic을 눌러 VBA 편집기를 엽니다.
만약 개발도구 탭이 안보인다면 파일 - 옵션 - 리본 사용자 지정에서 개발 도구 왼쪽의 체크 박스에 체크하면 됩니다.
왼쪽 위 프로젝트 창에서 '지번주소 결합 완성(2).xlsx)를 선택하고,
삽입 - 모듈을 눌러
모듈을 추가합니다.
엑셀 파일 아래에 모듈과 Module1이 추가되고, 오른쪽에는 빈 코드 창이 열립니다.
가. 해결책 1
(1) 코드
아래와 같이 코드를 작성하고,
Sub 주소결합()
Dim i As Integer, endRow As Integer
endRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To endRow
Range("E" & i) = Range("A" & i) & " " & Range("B" & i) & Range("C" & i) & _
IIf(Range("D" & i) > 0, "-" & Range("D" & i), "")
Next
End Sub
오른쪽 세모 모양의 실행 아이콘을 클릭한 후 왼쪽 엑셀 아이콘을 눌러 엑셀로 돌아가면
수식이 없이 결과값만 보입니다.
(2) 코드 설명
| Dim i As Integer, endRow As Integer i와 endRow 라는 변수를 정수 형식으로 선언하는 것입니다. |
| endRow = Cells(Rows.Count, "A").End(xlUp).Row endRow 깂을 Cells(Rows.Count, "A") 다시 말해 A열의 맨 아래 줄 셀에서 End(xlUp) 맨 위로 올라오는데, 문자나 숫자가 있는 행에서 셀 포인터가 멈춥니다. Row 멈춘 셀의 행 번호를 대입합니다. 결괏값은 위 파일의 경우 9가 됩니다. |
| For i = 2 To endRow Range("E" & i) = Range("A" & i) & " " & Range("B" & i) & Range("C" & i) & _ IIf(Range("D" & i) > 0, "-" & Range("D" & i), "") Next For ~ Next는 반복문이며, i값이 2부터 endRow까지 1씩 증가하면서 반복합니다. Range("E" & i), 다시 말해 E열 i행, Ei셀에 들어갈 값을 구하는데, Range("A" & i) & " " & Range("B" & i) & Range("C" & i) & A셀과 공백 한칸, 그리고 B와 C셀을 결합한 다음 IIf(Range("D" & i) > 0, "-" & Range("D" & i), "") 여기서 iif 함수는 엑셀의 if함수와 같은 것인데, 구분하기 위해 if 앞에 i를 붙인 것입니다. D셀 값이 0보다 클 경우는 하이픈과 D셀값을 결합하고, 아니면 빈 칸을 결합하므로 결합하지 않는 것과 같습니다. |
나. 해결책 2
iif 함수를 이용하지 않고,
If ~ Then ~ Else 문을 이용할 수도 있습니다.
가. 구문
구문은 아래와 같습니다.
If 조건 Then
실행문
[ ElseIf 조건2 Then
실행문2 ]
[ Else
실행문3 ]
End If
대괄호([ ]) 사이에 있는 것은 옵션이므로 반드시 있어야 하는 것은 아닙니다.
나. 코드
Sub 주소결합2()
Dim i As Integer, endRow As Integer
endRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To endRow
If Range("D" & i) > 0 Then
Range("E" & i) = Range("A" & i) & " " & Range("B" & i) & Range("C" & i) & _
"-" & Range("D" & i)
Else
Range("E" & i) = Range("A" & i) & " " & Range("B" & i) & Range("C" & i)
End If
Next
End Sub
① If Range("D" & i) > 0 Then : Di셀 값이 0보다 크다면
② Range("E" & i) = Range("A" & i) & " " & Range("B" & i) & Range("C" & i) & _
"-" & Range("D" & i) : Ei셀 값을 Ai셀값에 공백 한 칸과 Bi와 Ci셀 값 그리고, 하이픈과 Di셀 값을 결합해서 표시합니다.
③ Else : Di셀 값이 0이하라면, 다시 말해 0이거나 빈칸이라면
④ Range("E" & i) = Range("A" & i) & " " & Range("B" & i) & Range("C" & i) : : Ei셀 값을 Ai셀값에 공백 한 칸과 Bi와 Ci셀 값으로 하는 것입니다.
⑤ End If : If문을 닫는 것입니다.
다. 코드 실행
Sub 프로시저가 2개이므로
주소결합2 Sub 프로시저안에 커서를 놓고 실행한 후
엑셀로 돌아가 확인해보면
같은 결과가 나옵니다.
3. 특지 구분에 일반이 있는 경우
가. 해결책 3
해결책 2를 기본으로 하되,
산이 아닌 일반인 경우는 Bi셀 값을 연결하면 안되므로 이에 대한 if문을 추가해야 합니다.
코드는 아래와 같습니다.
Sub 주소결합3()
Dim i As Integer, endRow As Integer
endRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To endRow
If Range("i" & i) = "산" Then
If Range("K" & i) > 0 Then
Range("L" & i) = Range("H" & i) & " " & Range("i" & i) & Range("J" & i) & _
"-" & Range("K" & i)
Else
Range("L" & i) = Range("H" & i) & " " & Range("i" & i) & Range("J" & i)
End If
Else
If Range("K" & i) > 0 Then
Range("L" & i) = Range("H" & i) & " " & Range("J" & i) & _
"-" & Range("K" & i)
Else
Range("L" & i) = Range("H" & i) & " " & Range("J" & i)
End If
End If
Next
End Sub
If Range("i" & i) = "산" Then과 Else 아래 부분을 추가하고, 열이 A부터가 아니라 H열부터이므로 이에 맞게 열 이름을 변경했습니다.
If Range("i" & i) = "산" Then : i열 i행의 값이 "산"이라면
Else : i열 i행의 값이 "산"이 아니라면
If Range("i" & i) = "산" Then안의 if ~ then ~ else 문은 해결책2와 같고,
Else 문의 아래 코드만 특지구분("일반")인 i열 i행의 값을 결합하지 않도록 했습니다.
다. 코드 실행
Sub 프로시저가 3개이므로
주소결합3 Sub 프로시저안에 커서를 놓고 실행한 후
엑셀로 돌아가 확인해보면
일반인 경우는 일반을 빼고 결합됐고, 산인 경우만 "산"을 결합해서 표시하고 있고,
부번도 0보다 큰 경우만 하이픈과 연결해 부번을 표시하고 있습니다.
라. 부번 등 데이터를 변경하는 경우
아래와 같이 K9셀의 값을 0에서 1로 바꿔도 함수를 이용한 수식과는 달리 L9셀의 값이 자동으로 바뀌지 않습니다.
따라서, 해당 매크로를 실행해야 합니다.
개발도구 - 매크로를 누른 후
매크로 목록에서 주소결합3을 클릭하고,
오른쪽 실행 버튼을 클릭하면
L9셀의 주소가 산64에서 산64-1로 바뀝니다.
4. 파일 형식 변경
매크로가 포함된 경우 xlsx로 저장하면 작성된 매크로가 저장되지 않으므로
xlsm(매크로 사용 통합 문서) 형식으로 저정해야 합니다.
파일 - 다른 이름으로 저장을 누른 다음
파일 형식 오른쪽을 눌러 파일 형식 목록을 표시한 후
Excel 매크로 사용 통합 문서(*.xlsm)을 클릭하고,
저장 버튼을 누릅니다.
확장자가 xlsm으로 바뀌었습니다.
