주소를 PNU로 변환하기(2) - VBA
3. 특지 구분 연결하기
특지 구분 등을 구하는 사용자 정의 함수는 아래 글을 참고 바랍니다.
https://lsw3210.tistory.com/564
이번에는 사용자 정의 함수가 아니라 매크로로 특지 구분을 구하겠습니다.
그러나 구문은 사용자 정의 함수나 같은데,
사용자 정의 함수는 특지구분이란 변수로 값을 반환해야 하는데 반해서
매크로는 직접 처리하고 값을 반환할 필요가 없고,
사용자 정의 함수의 경우는 인수(아래 코드에서 '범위')를 직접 대입할 수 있는데 반해서,
매크로의 경우는 인수를 대입할 수도 있지만 본 예제의 경우는 반복 구문이기 때문에 인수를 직접 입력할 수 없다는 점입니다.
Function 특지구분(범위 As Range)
'범위(주소)에서 특지구분을 추출한다.
Dim 마지막공백 As Integer
Dim 지번 As String
마지막공백 = InStrRev(범위, " ")
If Mid(범위, 마지막공백 - 1, 1) = "산" Then
마지막공백 = InStrRev(범위, " ", 마지막공백 - 1)
End If
지번 = Mid(범위, 마지막공백 + 1)
If Left(지번, 1) = "산" Then
특지구분 = "산"
Else
특지구분 = "일반"
End If
End Function
매크로로 작성한 구문은 아래와 같습니다.
Sub pnu()
Dim endRow As Long, i As Long
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Sheets(1)
Set sht2 = Sheets(2)
sht1.Select
endRow = Range("a" & Rows.Count).End(xlUp).Row
Dim 마지막공백 As Integer
Dim 지번 As String
For i = 2 To endRow
Range("f" & i).FormulaArray = "=index(" & sht2.Name & "!" & Range("A:A").Address & _
", match(" & Range("b" & i).Address & "," & sht2.Name & "!" & Range("B:B").Address & ",0))"
마지막공백 = InStrRev(Range("A" & i), " ")
If Mid(Range("A" & i), 마지막공백 - 1, 1) = "산" Then
마지막공백 = InStrRev(Range("A" & i), " ", 마지막공백 - 1)
End If
지번 = Mid(Range("A" & i), 마지막공백 + 1)
If Left(지번, 1) = "산" Then
Range("f" & i) = Range("f" & i) & "2"
Else
Range("f" & i) = Range("f" & i) & "1"
End If
Next
End Sub
| 사용자 정의 함수 | 매크로 |
| Function 특지구분(범위 As Range) => 인수가 있음 |
Sub pnu() => 인수가 없음 |
| 마지막공백 = InStrRev(범위, " ") => 입력받은 인수를 이용해 값을 구함 ※ 아래도 몇 군데 더 있음 |
마지막공백 = InStrRev(Range("A" & i), " ") => 입력받은 인수가 없으므로 Range("A" & i)이라고 직접 범위 입력 ※ 아래도 몇 군데 더 있음 |
| If Left(지번, 1) = "산" Then Range("f" & i) = Range("f" & i) & "2" Else Range("f" & i) = Range("f" & i) & "1" End If |
지번에서 왼쪽 첫 번째 글자가 "산"이면 기존 값에 2를 붙이고, 아니면 1을 붙입니다.
특지 구분에 해당하는 코드는 "산"이 2(임야대장)이고, "산"이 아닌 것은 1(토지대장)입니다.
따라서, 위 코드를 실행하면
맨 뒤에 1 또는 2가 연결됩니다.
4. 본번 연결하기
논리는 사용자 정의 함수나 같은데,
본번을 네 자리 숫자로 만든다는 것이 다른 점이며,
그러기 위해서는 앞에 0 세 개를 연결한 다음 오른쪽 4개를 가져오도록 해야 합니다.
코드는 아래와 같습니다.
If Left(지번, 1) = "산" Then
Range("f" & i) = Range("f" & i) & "2" & Right("000" & Val(Mid(지번, 2)), 4)
Else
Range("f" & i) = Range("f" & i) & "1" & Right("000" & Val(Mid(지번, 1)), 4)
End If
위 코드를 수정해서 기존 코드의 뒷부분에
Right("000" & Val(Mid(지번, 2)), 4) 또는 Right("000" & Val(Mid(지번, 1)), 4)
를 붙였습니다.
위 코드를 실행하면 수식 입력줄에는 정상적으로 숫자가 보이는데, F열은 지수 형식으로 보입니다.
열 너비를 넓혀도 여전히 지수 형식으로 보입니다.
이럴 때는 표시 형식 '일반'을 '숫자'로 바꿔야 하며, 텍스트 형식으로 바꿔도 안됩니다.
그러면 정상적으로 숫자 형식으로 보이고, 본번이 4자리 숫자로 제대로 표시되고 있습니다.
5. 부번 연결하기
사용자 정의 형식과 논리는 같습니다.
부번은 "산"이 아니라 하이픈("-" 또는 "ㅡ")의 위치가 중요하기 때문에
별도 조건문으로 구성해야 하며, 작성된 코드는 아래와 같습니다.
하이픈위치 = WorksheetFunction.Max(InStr(1, Range("A" & i), "ㅡ"), _
InStr(1, Range("A" & i), "-"))
If 하이픈위치 > 0 Then
Range("f" & i) = Range("f" & i) & Right("000" & Val(Mid(Range("A" & i), _
하이픈위치 + 1)), 4)
Else
Range("f" & i) = Range("f" & i) & "0000"
End If
그리고, 윗부분 변수 선언하는 부분에
Dim 하이픈위치 As Integer라고 '하이픈위치' 변수를 정수 형식으로 선언하는 것은 사용자 정의 함수와 동일합니다.
| Range("f" & i) = Range("f" & i) & Right("000" & Val(Mid(Range("A" & i), _ 하이픈위치 + 1)), 4) |
기존 값에 "000"과 부번을 결합한 후 오른쪽 4개를 결합해서 처리 셀 Range("f" & i)에 값을 대입하는 것이며,
첫번째 줄 맨 뒤에 있는 _는 두 줄의 코드가 연결되어 있다는 의미입니다.
그런데 실행하면 부번이 있는데도 모두 네 자리 0으로 표시됩니다.
이것은 숫자 정밀도의 문제로서 숫자가 아닌 텍스트로 바꾸야 합니다.
따라서 맨 앞에 작은따옴표를 연결해야 하며, 아래와 같습니다.
Range("f" & i) = "'" & Range("f" & i) & Right("000" & Val(Mid(Range("A" & i), _
하이픈위치 + 1)), 4)
else문에도 작은따옴표를 연결하지 않으면 하나는 문자, 하나는 숫자로 보이므로 둘 다 연결하는 것이 좋습니다.
그리고 실행하면 아래와 같이 19자리로 된 PNU 코드가 완성됩니다.
완성된 코드와 파일은 아래와 같습니다.
Sub pnu()
Dim endRow As Long, i As Long
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Sheets(1)
Set sht2 = Sheets(2)
sht1.Select
endRow = Range("a" & Rows.Count).End(xlUp).Row
Dim 마지막공백 As Integer
Dim 지번 As String
Dim 하이픈위치 As Integer
For i = 2 To endRow
Range("f" & i).FormulaArray = "=index(" & sht2.Name & "!" & Range("A:A").Address & _
", match(" & Range("b" & i).Address & "," & sht2.Name & "!" & Range("B:B").Address & ",0))"
마지막공백 = InStrRev(Range("A" & i), " ")
If Mid(Range("A" & i), 마지막공백 - 1, 1) = "산" Then
마지막공백 = InStrRev(Range("A" & i), " ", 마지막공백 - 1)
End If
지번 = Mid(Range("A" & i), 마지막공백 + 1)
If Left(지번, 1) = "산" Then
Range("f" & i) = Range("f" & i) & "2" & Right("000" & Val(Mid(지번, 2)), 4)
Else
Range("f" & i) = Range("f" & i) & "1" & Right("000" & Val(Mid(지번, 1)), 4)
End If
하이픈위치 = WorksheetFunction.Max(InStr(1, Range("A" & i), "ㅡ"), _
InStr(1, Range("A" & i), "-"))
If 하이픈위치 > 0 Then
Range("f" & i) = "'" & Range("f" & i) & Right("000" & Val(Mid(Range("A" & i), _
하이픈위치 + 1)), 4)
Else
Range("f" & i) = "'" & Range("f" & i) & "0000"
End If
Next
End Sub