복 사해서 붙혀넣는 볌위 변경 VBA

VBA
작성자
마운틴
작성일
2022-02-21 12:47
조회
148
엑셀버전 : 엑셀2019

운영체제 : 윈도우10


안녕하세요/

지난번 bobo 고수님이 한번 리뷰해주신 구문인데 복사해서 붙혀넣은 범위 시작이

B8에서 B10부터 시작하도록 For문을 넣어 구문을 고쳐보려고했더니 빨간색 부분에서

에러가 나네요 -ㅜㅜ. 아래쪽에 다른 표가 또 있어 B열 전체 맨마지막까지 지정하면 안

되는 상활구요 도움 부탁드립니다

  • 구문 변경 :  B8~B19  ---> B10 ~B19 노란색 칠해진 부분

- 기존 구문 : Cells(19, 2).End(3).Offset(1) = Target.Value

- 변경 구문(에러) :

For i = 1 To 9

          '// cells((9+i, rngB) = Target.Value

Next i

 

<전체 구문>

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim rngA As Range
Dim i As Integer

Set rngA = Range("U6:U32")
Set rngB = Range("B10:B18")

Application.EnableEvents = False

If Not Intersect(Target, rngA) Is Nothing Then

For i = 1 To 9

'// cells((9+i, rngB) = Target.Value

Next i

End If

Cancel = True

Application.EnableEvents = True

End Sub

회원등급 : 12레벨
포인트 : 998 EP
총질문 : 67 개 (마감율 : 63%)
채택답변 : 0 개
전체 9

  • 2022-02-22 12:27

    cells((9+i, rngB) = Target.Value 이 부분의 인수가 형식에 맞지 않습니다. Cells(행, 열)에 Target.Value를 넣어라는 건데 rngB는 영역이지 열이 아닙니다. 만약 9+i행 B열에 넣고 싶은 거라면 그냥 cells(9+i, "B") = Target.Value 이렇게 하시면 됩니다. "("는 한 번만 입력해야 합니다.

    참고로 Set rngB = Range("B10:B18") 이 구문은 Range("B10:B18") 영역을 rngB 변수에 집어넣겠다는 뜻인데 변수 선언 Dim rngB As Range가 빠져 있습니다. 다른 구문이 더 있다면 모르겠지만 현재 기재해 주신 전체 구문 상에서 Set rngB = Range("B10:B18")는 전혀 쓰이지 않는 불필요한 구문 같아 보입니다.

    첨부파일의 매크로에는 외부 프러시저가 포함되어 있는 것 같습니다. 따라서 매크로가 정상 작동되는지 테스트가 불가했습니다.

    VBA 기초부터 차근차근 순서대로 공부해 보실 것을 권해 드립니다. 기초가 전혀 없는 상태에서는 다른 회원의 도움이 별로 도움이 되지 않아 보입니다.

     

     


    • 2022-02-22 17:00

      @나는나 님 넵~~자세한 설명 대단히 감사합니다 잘 이해되었습니다 왕초보가 구문을 수정해보려니 쉽지 않네요ㅜㅜ 제가 좀 더 많은 공부가 필요할 것 같습니다

      핵심은 현재 예제파일내 구문은 U열의 셀을 클릭하면 클릭된 값이 B8부터  붙혀넣기 되도록 되어있는데 노란색영역인 B10부터 붙혀넣기 되도록 바꾸고자 하는 것입니다

      vba는 sheet1(개발원장_양식)에 있고 나머지 빈 모듈은 실제 사용하는 작업파일을 예제파일로 만들면서 예제시트 외 다른 시트와 매크로를 삭제하고 올리다 보니 껍데기만 남아있는 것 같습니다

      붙혀넣는 위치 범위를 B10 : B18로 한정하는 방법을 잘 모르겠어요 일단 현재 구문의 Cells(19, 2).End(3).Offset(1) = Target.Value을 For문을 이용해 cells((9+i, "B") = Target.Value으로 고쳐보기도 했었는데 에러가 계속 나더라구요 ㅜㅜ 그리고 B열 밑부분에 표가 또 위치해있기도 해서 붙혀넣을 범위를 B10 : B18로 한정하기 위해 "B"를 쓰지 않고 일단 개념적으로 rngB로 집어넣었던 것인데 VBA 문법에는 안 맞는 것이었나 보네요

      첨부파일 : 마지막행누적붙혀넣기_문의3.xlsm


  • 2022-02-22 21:22
    Cells(19, 2).End(3).Offset(1) = Target.Value

    우선 이 구문의 의미는 19행 2열 즉, [B19]에서 위 방향으로 종단이동하여 데이터가 있는 마지막셀에서 다시 1행을 offset한 셀에 Target.Value를 넣으라는 뜻입니다. 만약[B18]까지 데이터가 다 입력되어 있는 상태라면 End(3).Offset(1)은 무조건 [B6]셀로 이동하므로 애초에 의도했던 것과 다른 결과가 되는 문제를 가지고 있습니다.

    구현하고자 하는 내용이 U열의 셀을 더블클릭하면 U열의 값을 B10~B18 9개 셀에 동일하게 입력하는 게 맞나요?

    For i = 1 To 9
        Cells(9 + i, "B") = Target.Value
    Next i

    그렇다면 처음에 말씀하신 대로 하셔도 무방합니다. 첫 번째 답글에서 말씀드렸듯이 cells((9+i, "B") = Target.Value에서 괄호 "("는 하나만 사용하셔야 합니다. 지금 "((" 두 개는 오류가 발생합니다. 만약 저렇게 U열의 값을 B10~B18 9개 셀에 동일하게 입력하는 거라면 그냥 간단하게

    Range("B10:B18") = Target.Value

    이렇게 입력하는 게 좋습니다. 굳이 For 순환문을 써서 9개 셀을 순환하면서 동일 작업을 9번 반복할 필요가 없이 그냥 한 번에 입력하는 게 좋습니다.

    첨부파일에 두 가지 모두 입력했으니 주석처리 해가며 두 가지 다 사용해보시고 어느 것이 용도에 맞는지 살펴보시기 바랍니다.

    참고로 모듈 맨 위에 Option Explicit 이렇게 선언하시면 모든 변수의 타입을 선언해야 합니다. 그렇지 않으면 오류를 띄우라는 옵션입니다. 따라서 For 순환문에 i를 사용하시려면 Dim i as Long과 같이 변수 선언을 반드시 하셔야 합니다. 이런 변수 선언을 일일이 하지 않으시려면 Option Explicit를 지우셔야 합니다.

    처음 작성하셨던 코드에서 에러가 발생하는 원인을 추측해보면

    1. Option Explicit를 입력했는데 i 변수를 선언하지 않아서
    2. cells((9+i, "B") = Target.Value와 같이 "("를 두 번 입력해서

    정도로 보입니다. 사소해 보이지만 문법이 맞지 않으면 프로그램은 정상적으로 명령을 실행하지 못 하니 기초 개념부터 차근차근 이해하시고, 이해되지 않는 구문에 대해서 도움을 구하시면 훨씬 더 문제 해결에 많은 도움이 되실 겁니다.

    Option Explicit
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
        Dim rngA As Range
        Dim i As Long
        
        Set rngA = Range("U6:U32")
        
        Application.EnableEvents = False
        
        If Not Intersect(Target, rngA) Is Nothing Then
        
            For i = 1 To 9
                Cells(9 + i, "B") = Target.Value
            Next i
            
    '        Range("B10:B18") = Target.Value
    '        Cells(19, 2).End(3).Offset(1) = Target.Value
        
        End If
        
        Cancel = True
        
        Application.EnableEvents = True
    
    End Sub

     

     

    첨부파일 : 마지막행누적붙혀넣기_문의3_수정.xlsm


    • 2022-02-22 23:59

      @나는나 님 친절하고 상세하신 설명 대단히 감사합니다 개념이 머리에 쏙 들어왔습니다

      결과적으로 제가 만든 For문은 일단 제가 원하던 것이 아니었네요 U열 전체의 내용을 B10:B18에 한번에 붙혀넣기하려 했다면  Cells((9 + i, "B") = Target.Value에서 처럼 "("를 1개만 쓰거나 가르쳐주신대로

      Range("B10:B18") = Target.Value

      Cells(19, 2).End(3).Offset(1) = Target.Value 이렇게 만들었어야 되는 거였군요

      그런데 U열  아무 셀이나 더블클릭할 때마다 그 셀값 1개씩만 B10부터 B18까지 순차적으로 붙혀넣기가 되는 걸 원하는 것이어서

      일단 Offset(1)을  Offset(3) 으로 수정해 봤더니 Offset함수 속성상 당연한 거겠지만;첫번째 더블클릭시에는 원하는대로 B10부터 붙혀넣기가 되긴 하는데 2번째 더블클릭부터는 계속 3칸 아래인 B13 B16...이렇게 붙혀넣기 되네요 2번째 더블클릭부터는 원래 Offset(1)을 적용했을때 처럼 B11 B12 이렇게 붙혀넣기 되도록 추가 명령문을 넣거나
      아니면 처음부터 offset(1)을 그대로 쓰되 첫번째 더블클릭시에만 B10으로 붙혀넣기되도록하는 명령문을 넣어야 할텐데 방법을 잘 모르겠네요

       

       

      우선 '{"


  • 2022-02-23 02:05
    채택된 답변

    질문하실 때 가급적 필요한 내용을 명확하게 전달하셔야 문제 해결이 되는 답변을 '한 번에' 받으실 수 있습니다. 여러 가지 복합적인 요소가 있는 질문을 하시면 그건 그냥 '코딩을 짜주시면 안 될까요?' 하는 것과 비슷하고 정성껏 답변을 드려도 도움이 안 되는 경우가 많습니다.

    Cells(19, 2).End(3).Offset(1) = Target.Value

    기본은 이걸 사용하시면 맞습니다. 다만 [B19] 셀에서 위 방향으로 종단이동할 때 비어있지 않은 마지막 셀이 무엇이냐에 따라서 Target.Value가 들어갈 셀이 매번 달라지겠지요.

    만약 [B8]이나 [B9]가 비어있다면 위 구문으로는 원하시는 시작셀 [B10]을 정확히 지정할 수 없습니다. 그럼 if문을 사용해서, [B10]이 빈 셀이면 [B10]에 입력하고, 빈 셀이 아니면 위 구문을 사용해라, 라고 하면 되겠네요.

    If [B10] = "" Then [B10] = Target.Value Else [B19].End(3)(2) = Target.Value

    U열을 더블클릭할 때마다 [B10]이 빈 셀인지 아닌지 확인해서 입력받을 셀을 결정하기 때문에 의도하신 셀에 U열의 더블클릭한 셀 값 한 개만 입력이 됩니다.

    참고로 "[B19].End(3)(2) = Target.Value"는 "Cells(19, 2).End(3).Offset(1) = Target.Value"와 동일한 의미입니다. [B19]는 Cells(19, 2) 또는 Range("B19")와 동일한 약식입니다. [B19].End(3)(2)의 (2)는 Item Property라는 메서드인데 기준셀의 (행, 열)을 (1,1)로 보고 (2)는 아래 행으로 한 칸 이동한다는 뜻입니다.(열은 이동 없어서 생략함) 즉 B19셀에서 위 방향으로 종단이동한 후 그 셀의 한칸 아래에 있는 셀을 의미합니다. Offset은 기준셀의 (행, 열)이 (0,0)인 점과 차이가 있습니다. 그래서 Offset을 사용할 때는 (1)이 들어가는 겁니다.

    위 If문에서 한 가지 남은 문제는 [B10]부터 [B18]까지 모두 채워진 상태에서 U열을 더블클릭하면 [B19]에서 위로 종단이동한 후 한 행 내려오니 [B11]에 덮어쓰게 됩니다. 이 부분도 마찬가지로 if문을 사용해서 위의 if문과 중첩해서 만들면 해결이 되겠지요. 이 부분은 스스로 해결해 보시기 바랍니다. 팁을 드리자면 [B18]이 빈 셀인지 아닌지 확인해서 빈 셀이 아니면 어떤 명령을 실행할지 짜면 되겠지요.


    • 2022-02-23 13:51

      @나는나 님 넵~~대단히 감사합니다 IF문을  쓰니 문제가 잘 해결되었네요

      주신 숙제 관련해서 18행까지 꽉 차면 더 이상 B열 종단으로 테이타가 입력되면 안되기 때문에 좀 더 연구해서 댓글로 그 결과를 달도록 하겠습니다 감사합니다


    • 2022-02-24 01:01

      @나는나 님 에고~중첩 IF문에 대해 이리저리 공부도 하고 구문도 짜봤는데 감이 부족해서 인지 작동이 안되네요

      If Not Intersect(Target, rngA) Is Nothing Then

      If [B10] = "" Then [B10] = Target.Value

      ElseIf [B18] <> "" Then Exit Sub

      Else [B19].End(3)(2) = Target.Value

      End If

      End If


      • 2022-02-24 11:12

        @마운틴 님 제가 자꾸 기초 공부부터 먼저 하시라는 게 이런 이유입니다. If문은 반드시 End if와 함께 '블록' 단위로 구성되어 줄바꿈을 해야 문법에 맞습니다. 그렇게 하지 않으시면 에러가 발생합니다.

            If Not Intersect(Target, rngA) Is Nothing Then
                If [B10] = "" Then
                    [B10] = Target.Value
                ElseIf [B18] <> "" Then
                    Exit Sub
                Else
                    [B19].End(3)(2) = Target.Value
                End If
            End If

        다만 명령줄이 한 줄인 경우 같은 줄에 사용 가능하고 End if를 생략할 수 있습니다.

        예) If [B10] = "" Then [B10] = Target.Value Else [B19].End(3)(2) = Target.Value

        또한 매우 중요한 문제가 있습니다. Exit Sub 명령으로 중간에 멈추게 할 경우 그 앞에 이미 실행된 Application.EnableEvents = False를 다시 True로 못 바꿔서, 엑셀의 기본값인 이벤트를 사용 못 하게 한 채로 종료하면 그 다음부터는 이 이벤트가 전혀 동작하지 않습니다. 그래서 프러시저를 종료하기 전에 다시 기본값인 True로 변경해주는 겁니다. 마찬가지로 Cancel = True 를 실행하지 않고 Exit Sub 하게 되면 셀 안에 커서가 깜빡거리는 상태로 남게 됩니다. 그래서 프러시져를 종료하기 전에 꼭 Cancel = True를 넣어줘야 합니다.

        Worksheet에서 매크로를 코딩하실 때 핵심은 정작 실행할 명령어보다 예외 처리 또는 에러 처리가 중요합니다. 조건에 맞지 않으면 Exit Sub할 내용을 초반에 두고 "조건이 부합하면 > 이벤트 중지 > 실행할 명령구문 > 이벤트 재개 > Exit Sub" 이 순서로 진행해야 합니다.

            Set rngA = Range("U6:U32")
        
            If Intersect(Target, rngA) Is Nothing Then Exit Sub'//<==기본 패턴
            If [B18] <> "" Then Exit Sub'//<==기본 패턴
            
            Application.EnableEvents = False'//<==기본 패턴
                
            If [B10] = "" Then [B10] = Target.Value Else [B19].End(3)(2) = Target.Value
            Cancel = True
            Application.EnableEvents = True'//<==기본 패턴

        다만 이번의 경우 Worksheet_BeforeDoubleClick이므로 더블클릭한 커서 처리를 위한 Cancel 인수도 함께 처리해줘야 합니다. 그럴 때는 단순히 Exit Sub로 끝내면 안 되고 에러처리를 해줘서, 중단된 이벤트를 복원하고 Cancel도 처리해준 다음 Exit Sub 하게끔 해야 합니다.

            Set rngA = Range("U6:U32")
            
            If Intersect(Target, rngA) Is Nothing Then GoTo ErrHandler '//ErrHandler 라벨로 점프하라
            If [B18] <> "" Then GoTo ErrHandler
            
            Application.EnableEvents = False
            
            If [B10] = "" Then [B10] = Target.Value Else [B19].End(3)(2) = Target.Value
            
        ErrHandler:
            Cancel = True
            Application.EnableEvents = True

        예전에 여기 Q&A게시판에 비슷한 내용의 질문에 제가 답변한 내용도 있습니다. 오빠두 사이트나 네이버, 구글, 유튜브 등에서 우선 검색하면서 문법과 원리를 이해하고 시도해보다가 막히는 부분에서 도움을 구하시는 게 좋을 듯합니다. 스스로 이해하지 않은 상태에서는 다른 사람이 짜준 구문을 살짝 변경해야 할 때마다 지금처럼 계속 오류를 만나게 될 겁니다.

         


        • 2022-02-24 16:05

          @나는나 님 네 감사합니다

          그러지 않아도 이리저리 연구해 보면서 의문이 들었던 부분들을 깔끔히 잘 짚어  주셨네요 논리구조상 Go To~이런 문구가 필요할 듯 했는데 Go To  ErrHandler 이런 문구가 있었군요 바쁘실텐데 하나하나 설명해 주셔서 깊히 감사를 드립니다 기초를 제대로 익히기도 전에 실무적으로 급히 처리해야할 상황이 발생하다보니 우물가에서 숭늉찾는 상황이 되었을 수도 있는데  말씀하신 대로 꾸준히 기초 공부를 병행해 나가도록 하겠습니다 감사합니다


전체 6,463
번호 카테고리 제목 작성자 작성일 추천 조회
알림
📑 MS에서 특별한 라이브를 진행합니다! - 애저 SQL-엑셀 실시간 연동 프로그램 만들기!
오빠두엑셀 | 2022.05.24 | 추천 3 | 조회 86
오빠두엑셀 2022.05.24 3 86
공지사항 함수/공식
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (65)
오빠두엑셀 | 2021.10.28 | 추천 80 | 조회 4485
오빠두엑셀 2021.10.28 80 4485
34391 기능/도구
New F1 키 누르면 나오는 도움말 끄는 단축키는 없나요?
우렁각시탈 | 12:00 | 추천 0 | 조회 3
우렁각시탈 12:00 0 3
34390 함수/공식
New 셀이 빈 경우 텍스트조인이 실행되지 않도록 하는 방법 첨부파일
forest choi | 11:56 | 추천 0 | 조회 5
forest choi 11:56 0 5
34387 VBA
New 개별공시지가조회 셀레니움 첨부파일
나야 | 10:50 | 추천 0 | 조회 7
나야 10:50 0 7
34378 기능/도구
New 특정 글자의 데이터만 따로 분리할 수 있는 방법이 있을까요? (3) 답변완료
dfgkdflglkdf | 01:08 | 추천 -1 | 조회 38
dfgkdflglkdf 01:08 -1 38
34376 VBA
New VBA 동적 범위 테두리 만들기 첨부파일
라바 | 2022.05.24 | 추천 -1 | 조회 30
라바 2022.05.24 -1 30
34372 VBA
New VBA Do until loop 문중에서 출하지시서 코드를 빌려 짜는데.. 한계가 왔습니다. 첨부파일 (2)
pk2616 | 2022.05.24 | 추천 0 | 조회 28
pk2616 2022.05.24 0 28
34369 기능/도구
New 하이퍼링크 (1)
쎄이 | 2022.05.24 | 추천 0 | 조회 19
쎄이 2022.05.24 0 19
34367 VBA
New 짝수 페이지 로고 크기 변경 매크로를 어떻게 만들어야 할까요? 첨부파일
꽁냐리 | 2022.05.24 | 추천 0 | 조회 20
꽁냐리 2022.05.24 0 20
34365 VBA
New 정해진 양식에 여러 부서에서 부분부분 작성된 자료를 취합하는 방법이 궁금합니다. 첨부파일 (3)
nam**** | 2022.05.24 | 추천 -1 | 조회 30
nam**** 2022.05.24 -1 30
34364 함수/공식
New 엑셀 if수식 문의 드립니다. (1)
달려라탁구 | 2022.05.24 | 추천 0 | 조회 31
달려라탁구 2022.05.24 0 31
34361 함수/공식
New "06w 2"에서 "2"만 추출하여 총합을 구하는 방법 첨부파일 (1) 답변완료
forest choi | 2022.05.24 | 추천 0 | 조회 39
forest choi 2022.05.24 0 39
34360 함수/공식
New 특정 셀만 기입하게 할 수 있을까요? (셀 잠금 말고) 첨부파일
신지영 | 2022.05.24 | 추천 0 | 조회 31
신지영 2022.05.24 0 31
34356 함수/공식
New 날짜(월) 계산하는 문제 관련 (5) 답변완료
nowhs | 2022.05.24 | 추천 1 | 조회 46
nowhs 2022.05.24 1 46
34350 함수/공식
New 강의별 출석부를 만들고 싶어요 첨부파일 (2) 답변완료
Blak case | 2022.05.24 | 추천 0 | 조회 50
Blak case 2022.05.24 0 50
34348 기능/도구
New 다른 통합 문서로 시트 복사시 함수 파일명 관련 (4) 답변완료
허경일 | 2022.05.24 | 추천 0 | 조회 35
허경일 2022.05.24 0 35
34345 파워쿼리/피벗
New 각 테이블 마다 중복제거관련 (12) 답변완료
엑셀지킴이 | 2022.05.24 | 추천 0 | 조회 51
엑셀지킴이 2022.05.24 0 51
34341 기능/도구
New 엑셀 시트 글자크기 및 사이즈 조정 되나요?
DOUKCHEON 이재형 | 2022.05.24 | 추천 0 | 조회 30
DOUKCHEON 이재형 2022.05.24 0 30
34340 함수/공식
New 여러개의 열에 만족하는 값 구하기 (4) 답변완료
프래이야 | 2022.05.24 | 추천 0 | 조회 45
프래이야 2022.05.24 0 45
34338 문서서식
New 선택한 셀 절대값으로 한번에 바꾸는 방법 (2)
DOUKCHEON 이재형 | 2022.05.24 | 추천 0 | 조회 44
DOUKCHEON 이재형 2022.05.24 0 44
34337 구글시트
New 발주대비 입고일자를 카운팅 하고싶습니다. (2) 답변완료
김성민 | 2022.05.24 | 추천 0 | 조회 35
김성민 2022.05.24 0 35
34336 VBA
New 다른 시트의 두 셀 연동
kido**** | 2022.05.24 | 추천 0 | 조회 39
kido**** 2022.05.24 0 39
34334 VBA
New VBA 검색창을 만들었는데 엔터가 되지 않습니다..! (2)
김수달 | 2022.05.24 | 추천 0 | 조회 42
김수달 2022.05.24 0 42
34332 구글시트
New 2개의 조건을 충족하는 조건 값을 다른 시트에서 끌어 올려면 어떻게 해야 할까요? (1)
흰별이 | 2022.05.24 | 추천 0 | 조회 26
흰별이 2022.05.24 0 26
34330 함수/공식
New 엑셀에 수식 합치기 기능이 있을까요? 첨부파일 (2)
크미누크르 | 2022.05.24 | 추천 0 | 조회 55
크미누크르 2022.05.24 0 55
34328 차트/그래프
New 방문하는 고객 누적막대그래프 및 최저, 최고 방문객 매장 리스트 첨부파일 (2)
사막여우 | 2022.05.23 | 추천 0 | 조회 32
사막여우 2022.05.23 0 32
34326 문서서식
New 오빠두 강의 중 마우스 형관펜 기능에 대해서 질문드립니다.
월마리아 | 2022.05.23 | 추천 0 | 조회 28
월마리아 2022.05.23 0 28
34324 문서서식
New 하이퍼링크 셀 선택 첨부파일 (2) 답변완료
쎄이 | 2022.05.23 | 추천 0 | 조회 35
쎄이 2022.05.23 0 35
34322 기능/도구
New 시트 갈때마다 맨위로 이동시키는 방법 (1)
naver_60f182128c054 | 2022.05.23 | 추천 0 | 조회 35
naver_60f182128c054 2022.05.23 0 35
34318 대시보드
New 생일명단 및 육아휴직명단 추출 첨부파일 (3) 답변완료
사막여우 | 2022.05.23 | 추천 0 | 조회 59
사막여우 2022.05.23 0 59
34311 문서서식
New [조건부서식] 기초 질문 첨부파일 (1)
일신우일신J | 2022.05.23 | 추천 0 | 조회 34
일신우일신J 2022.05.23 0 34