날짜 변환 - DateValue 함수, 선택하여 붙여넣기 VBA(2)

1편은 날짜를 변환할 열에 데이터가 연속적으로 있다고 가정하고 만든 것인데,

날짜가 중간에 비어 있다면 End(xlDown)했을 때 데이터가 있는 마지막 행으로 이동하는 것이라 데이터가 없는 중간에 멈추게 됩니다.

날짜변환예제2.xlsm

0.02MB

 

 

 

아래와 같이 A6셀에 데이터가 없을 경우

 

날짜변환 매크로를 실행하면 A6셀 전까지만 날짜변환이 처리됩니다.

공백 아래는 처리되지 않음

 

1. 원인 분석 1

 

첫 번째 If문에 중단점을 설정한 후 실행하고 A열을 선택한 후 확인 버튼을 누르

 

If문에서 실행이 멈추게 되는데 F8을 눌러 한 단계씩 실행하면 

 

직접 실행 창에 물음표 다음에 col_num(1, 1).End(xlDown).Row을 붙여 넣고 엔터키를 누르면 2가 표시되고,

물음표 다음에 Cells(Rows.Count, col_num.Column).End(xlUp).Row을 붙여 넣고 엔터키를 누르면 12가 표시되어 값이 다르므로

 

직접 실행창에서 결과값 알아내기

 

Else문으로 이동합니다.

그리고, prev_range는 2행 아래 줄이 되므로 A3셀이 됩니다.

 

다시 F8키를 누르면 데이터 포함해 2줄을 초과하므로 다음 if문의 Else문을 타게 됩니다.

그러면 prev_value=2000-01-01이 되고,

prev_range에 1을 대입한 후 복사합니다.

 

그리고, 다시 If문을 거치는데, 제목 표시줄 포함 3줄 이상이므로 다시 Else문을 탑니다.

그런데 문제는 여기서 발생합니다.

 

Set paste_cells = Range(prev_range.Offset(1, 0), prev_range.Offset(1, 0).End(xlDown))

paste_cells란 범위를 설정하는데, 데이터 첫 번째 셀에서 한 줄 아래 셀부터, 거기서 다시 맨 아래 줄까지 범위를 설정하다 보니 

 

F8키를 누른 후

직접 실행창에 ? paste_cells.Address라고 입력한 후 엔터 키를 누르면 $A$$:$A$5가 범위로 지정되기 때문에 A5셀까지만 날짜 변환이 제대로 되고, 그 아래는 날짜 변환이 안된 것입니다.

 

2. 대책 1

그렇다면 붙여 넣을 범위를 지정할 때 데이터가 있는 셀 아래 셀부터 시트의 맨 아래에서 위로 올라왔을 때 걸리는 셀까지로 범위를 지정하면 됩니다.

 

따라서, 명령문을 아래와 같이 수정하면 됩니다.

Set paste_cells = Range(prev_range.Offset(1, 0), Cells(Rows.Count, col_num.Column).End(xlUp))

 

그리고, 다시 실행한 후 paste_cells의 주소를 확인하면 $A$4:$A$12로 제대로 되고, F5키를 눌러 모두 실행하면

붙여넣을 범위 주소 알아내기

 

모든 셀의 텍스트 형 날짜가 날짜 형 날짜로 변환되는데 빈칸은 1900-01-00이라는 이상한 날짜로 됩니다.

공백이 숫자0에 해당하는 1900-01-00으로 표시됨

 

이 때는 홈 탭에서 찾기 및 선택을 누른 후 바꾸기를 클릭하거나, Ctrl + H키를 누른 후 찾을 내용에 1900-01-00을 입력하고, 바꿀 내용을 그대로 두고 '모두 바꾸기' 버튼을 누르면 

1900-01-00을 빈 칸으로 대체하기

 

 

3. 원인분석 2

이번에는 제목 표시줄을 1행으로 바꾼 후 실행하

 

"런타임 오류가 발생했다"라고 하면서 "형식이 일치하지 않는다"라고 합니다. 디버그 버튼을 누르면

형식이 일치하지 않음

 

 

30행이 노란색으로 표시됩니다. 

 

직접 실행창에서 prev_range의 주소를 확인해 보니 A2셀이 아니라 $A$5라고 합니다.

 

prev_range 설정이 잘못된 것입니다.

 

표준 도구모음에서 네모 아이콘을 누르거나, 실행 메뉴에서 재설정 메뉴를 눌러 실행을 멈추고,

 

첫 번째 If문에 중단점을 설정하고,

 

날짜 변환 매크로를 실행한 후 A열을 선택하고 확인 버튼을 누르면 아래와 같이 중단점에서 실행이 멈추는데,

 

직접 실행창에서 col_num(1, 1).End(xlDown).Row와 Cells(Rows.Count, col_num.Column).End(xlUp).Row의 값을 확인해 보니 4와 11로 다릅니다. 중간에 공백이 없다면 둘 다 11이 됐을 텐데 공백이 있어서 If문 다음을 실행하지 않고, Else문 다음을 실행해서 그런 것입니다.

 

4. 대책 2

"첫 줄이라면"이라고 조건을 바꾸면  됩니다.

 

"첫 줄이라면"은 "첫 번째 셀에 값이 있다"면으로 조건을 수정하면 되므로

If Len(col_num(1, 1)) > 0 Then이 됩니다.

 

이렇게 수정하고

실행하면 문제없이 잘 됩니다.

 

프로그램은 경우의 수와의 싸움입니다.

날짜변환예제2(완성).xlsm

0.02MB