'업무 능력 향상'에 해당되는 글 240건

색상별로 시간합계 구하기

문의사항이 같은 색으로 더해진 시간들만 더하고 싶을 때

같은 색상별로 시간의 합계를 구하고 싶다면 어떻게 해야 할까요?

먼저 서로 다른 색상을 구해야 합니다. 사실 이 부분 테스트한다고 잘못 생각해서 시간허비 많이 했네요

처음부터 Msgbox 를 사용해서 확인해 가면서 했으면 금방 찾아서 시간낭비는 안했을텐데 ㅠㅠㅠ

Option Explicit
Sub SameColor_Sum()
    Dim rngC, rngT As Range    '// 각 셀을 넣을 변수
    Dim rngData As Range      '// 전체 데이터 영역을 넣을 변수
    Dim rngVariable As Range  '// 색상 데이터 영역 변수
    Dim i As Long
    Dim Max_Cnt As Double   '// 변수 유형을 Integer 로 지정하면 오류 발생
   
    Application.ScreenUpdating = False        '화면 업데이트 (일시) 정지   
    '-------------------- D열에 색상 및 색상값 복사 ----------------------------------
    Set rngData = Range([B2], Cells(Rows.Count, "B").End(3))
    For Each rngC In rngData
        rngC.Offset(0, 2).Value = rngC.Interior.ColorIndex
        rngC.Offset(0, 2).Interior.ColorIndex = rngC.Interior.ColorIndex
    Next rngC
  
    '------------------- D열의 중복값 모두 제거 및 오름차순 정렬 ----------------------
    Set rngVariable = Range("D2", Cells(Rows.Count, "D").End(3))  'D열 데이터영역을 변수에
    For Each rngT In rngVariable                               'D열 각셀을 순환
        If Application.CountIf(rngVariable, rngT) > 1 Then  '각셀의 countif 값을 변수에
            rngT.Resize(, 1).Clear   '그 셀의 데이터를 삭제
        End If
    Next rngT
    Range([D2], Cells(Rows.Count, "D").End(3)).Sort [D2], 1   '//오름차순 정렬
   
    '---------------------- 같은 색상의 값을 더하기 -----------------------------------
    Range([E2], Cells(Rows.Count, "E").End(3)).ClearContents '// 값 초기화
    For Each rngT In rngVariable
        For i = 2 To Cells(Rows.Count, "B").End(3).Row
            If rngT.Value = Cells(i, "B").Interior.ColorIndex Then
                   Max_Cnt = Max_Cnt + Cells(i, "B").Value
                   rngT.Offset(0, 1).Value = Max_Cnt
            End If
        Next i
        Max_Cnt = 0
    Next rngT
   
    '---------------- D열 색상만 남기고 값은 지우기 -------------------------------------
    For Each rngT In rngVariable              'D열 각셀을 순환
        rngT.ClearContents
    Next rngT
       
    With ActiveSheet   '// 결과를 표시할 Sheet 선택
        .Cells(1, "D").Value = "색상"
        .Cells(1, "E").Value = "시간합계"
        '--------------- 가운데 정렬, 선그리기 ----------------------------
        .Range(.[D1], .Cells(Rows.Count, "E").End(3)).HorizontalAlignment = xlCenter
        .Range(.[E2], .Cells(Rows.Count, "E").End(3)).NumberFormat = "h:mm:ss" '// 시간표시
        .Range(.[D1], .Cells(Rows.Count, "E").End(3)).Borders.LineStyle = 1     '// 사용영역 선그리기
        .Range(.[D1], .Cells(Rows.Count, "E").End(3)).Sort .[E2], 2    '// 값을 내림차순으로 정렬
    End With
       
    '------------------ 개체변수 초기화(메모리 비우기) -----------------------------------
    Set rngVariable = Nothing
    Set rngData = Nothing
   
    MsgBox "작업완료"   
End Sub

SameColor_Sum.vbs

Sum_of_Same_Color_Area.xlsm

첨부된 예제 받아서 확인해보세요.

한줄 한줄 실행하는 건 F8키 누르면 됩니다.

Vlookup를 이용한 현재 재고 파악

현재 재고를 파악하는 건데요. 기존 재고에서 일간 판매량이 있을 경우 일간 판매량을 빼주면 현재 재고가 됩니다.

Sub Vlookup_Sales()
'Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

    Dim lookup_value As Range
    Dim table_array As Range
    Dim rngC As Range
    Dim varTemp As Variant
   
    Set lookup_value = Range("A3:A12")
    Set table_array = Range("A18:C22")
    'Set table_array = Workbooks("VLookup_Sample_v1.xlsx").Sheets("work").Range("A2:B31")
      '// 다른 파일에 있는 값과 비교하고자 할 때
     
    For Each rngC In lookup_value
        varTemp = Application.VLookup(rngC, table_array, 3, 0)
        '// 서로 일치하는게 있으면 table_array 의 세번째 열을 가져와라
        If IsError(varTemp) Then   '// 에러 #N/A 일 경우
            rngC.Offset(0, 3).Value = rngC.Offset(0, 2).Value
        Else
            rngC.Offset(0, 3).Value = rngC.Offset(0, 2).Value - varTemp
        End If
    Next rngC
   
    Set lookup_value = Nothing '// 변수 초기화
    Set table_array = Nothing
   
End Sub

예제 파일 첨부 합니다. VBA 코드는 예제 파일에 포함되어 있습니다.

Vlookup_Sales.xlsm

다른파일과 중복검사하는 Vlookup VBA

엑셀의 다른 파일에 있는 데이터와 중복되는 데이터가 있는지 검사하고 싶을 때 사용하는 VBA 입니다.

현재 입력한 데이터가 중복되는지 여부를 알고 싶을 때가 있더라구요.

VBA 소스코드 입니다.

어떻게 수정해서 사용해야 하는지 감이 잡히시죠?

첨부된 파일에서 동작하는 VBA 와 위 예시로 작성한 소스코드 VBA 를 약간 다르게 했는데 아주 쉬운거라서 금방 이해가 되실 겁니다.

중복검사 아이콘만 누르면 순식간에 중복된 값을 찾아서 표시를 해줍니다.

중복되지 않은 것은 빈공간으로 표시가 되구요.

VLookup VBA-02.vbs

VLookup VBA-02.xlsm

VLookup_Sample_v1.xlsx

InStr 를 이용한 셀 분리

이번에는 InStr 함수를 이용하여 셀 분리를 해보겠습니다.

범위 지정은 Range([A2], Cells(Rows.Count, "A").End(3))

구분자의 위치값을 반환하여 Left 함수 및 Mid 함수를 이용하여 원하는 구간을 잘라낼 수 있다.

Sub Cell_Split()
    Dim rngC    As Range   '// 한 Cell 씩 변하는 변수 지정
    Dim rngTarget As Range '// 대상 범위 지정변수
    Dim Split_L As Long    '// 구분자 위치 변수
    Dim deLimiter As String  '// 문자 구분자 변수
   
    Application.ScreenUpdating = False  '//화면 업데이트 (일시)정지
    Set rngTarget = Range([A2], Cells(Rows.Count, "A").End(3))
    '// A열 2행부터 값이 들어있는 마지막 행까지의 범위를 지정
  
    deLimiter = "/"       '//문자 구분자
    For Each rngC In rngTarget       
        Split_L = InStr(rngC.Value, deLimiter)
        '// InStr 함수 : 한 문자열 안에 특정 문자열이 처음으로 나타난 위치값을 반환       
        If Split_L > 0 Then
            rngC.Offset(0, 2).Value = Mid(rngC.Value, Split_L + 1, Len(rngC.Value) - Split_L)
            'rngC.Offset(0, 1).Value = Left(rngC.Value, Split_L - 1)  '// 구분자 앞부분을 표시
        End If
    Next rngC
    
    Set rngTarget = Nothing '// 변수 초기화
    Columns("C:G").AutoFit
End Sub

Cell_Split_VBA_instr.vbs

Cell_Split_VBA_instr.xlsm

rngC.Offset(0,2) 의 의미는 rngC 변수로부터 우측으로 2열 이동하라는 의미다.

만약 현재 셀에다가는 구분자의 왼쪽 값을 표시하고 싶다면

rngC.Offset(0,1).Value = Mid(rngC.Value, Split_L + 1, Len(rngC.Value) - Split_L)

rngC.Value = Left(rngC.Value, Split_L - 1)

로 변경해주면 된다.

우측에 표시될 값부터 먼저 표기한 이유는 위아래 순서를 변경해보면 알아요.

하지만, split 함수를 이용하면 훨씬 더 깔끔하게 해결할 수 있습니다.

첨부파일에 아래 코드를 추가로 포함시켜 놓았습니다.

Sub 셀분리()
    Dim rngC, rngAll As Range
    Dim v
    Set rngAll = Range([A2], Cells(Rows.Count, "A").End(3))
    Range([C2], Cells(Rows.Count, "D")).ClearContents   '// 표시할 영역 데이터 전부 지우기
    On Error Resume Next
    For Each rngC In rngAll
        v = Split(rngC, "/")
        Cells(rngC.Row, "C") = Trim(v(0))
        Cells(rngC.Row, "D") = Trim(v(1))
    Next rngC
    Set rngAll = Nothing
    Columns("C:G").AutoFit
End Sub

셀 분리하여 다른 시트에 뿌리기

셀 분리하여 다른 시트에 뿌리는 VBA 입니다.

바로 앞의 게시물과 동일한데 결과만 다른 시트에 뿌리는 것입니다.

Sub Cell_MultiSplit()
    Dim rngC    As Range   '// 한 Cell 씩 변하는 변수 지정
    Dim rngTarget As Range '// 대상 범위 지정변수
    Dim varTemp() As String  '// 전체영역을 넣기위한 variant형 string 변수
    Dim deLimiter As String  '// 문자 구분자 변수
   
    Application.ScreenUpdating = False  '화면 업데이트 (일시)정지

    Set rngTarget = Columns(1).SpecialCells(2)
    '// SpecialCells(2) : 상수가 들어있는 셀
  
    deLimiter = "/"             '//문자 구분자
        For Each rngC In rngTarget
            varTemp = Split(rngC, deLimiter)       '//선택한 셀을 쪼개서 배열에 넣음
            'rngC.Offset(, 2).Resize(1, UBound(varTemp) + 1) = varTemp   '//현재 Sheet 에 뿌림
            '// Resize(RowSize,ColumnSize) : 지정된 범위의 크기를 조정
            '// Rowsize : 새 범위의 행 수를 지정
            '// ColumnSize : 새 범위의 열 수를 지정
            '// Ubound(arrayname, dimension) : 배열에서 지정된 차원의 최대 범위를 Long으로 반환
            '// arrayname : 배열 변수의 이름
            '// dimension 은 생략하면 1차원을 의미
            With Sheet2
                .Cells(rngC.Row, "C").Resize(1, UBound(varTemp) + 1) = varTemp
                .Columns("C:G").AutoFit
            End With
        Next rngC
    Set rngTarget = Nothing '// 변수 초기화
    MsgBox "작업완료"
   
End Sub

Cell_Split_VBA_othersht.vbs

Cell_Split_VBA_othersht.xlsm

Cells(행, 열) 이 For Each 구문에서 rngC 의 값이 변하면서 변동되므로

Cells(행, "C") 로 행은 변하는 값이므로 rngC.Row 로 현재 행의 값을 구함

열은 뿌리고 싶은 열을 직접 지정함

[VBA] 셀 분리하여 현재 시트에 뿌리기

엑셀에서 문자를 분리하고 싶은 경우가 있습니다.

이럴 경우 분리하는 방법으로 Split 함수를 이용하는 방법도 있고, Instr 함수를 이용하는 방법도 있습니다.

Instr 함수는 분리 구분자의 위치를 반환하여 MID함수랑 같이 사용하여 분리할 수 있습니다.

단순하게 구분자(delimiter)로 분리만 한다면 Split 함수가 편합니다.

분리한 것을 현재 셀에다가 할 것인가 아니면 옆에 있는 셀에다가 표시할 것인가를 먼저 정합니다.

옆셀에다가 뿌리는 것은 Offset 함수를 사용합니다.

Offset(,2) 는 Offset(0,2) 로 같은 행, 열 2 이동하라는 의미입니다.

Sub Cell_MultiSplit()
    Dim rngC    As Range   '// 한 Cell 씩 변하는 변수 지정
    Dim rngTarget As Range '// 대상 범위 지정변수
    Dim varTemp() As String  '// 전체영역을 넣기위한 variant형 string 변수
    Dim deLimiter As String  '// 문자 구분자 변수
   
    Application.ScreenUpdating = False  '// 화면 업데이트 (일시)정지

    Set rngTarget = Columns(1).SpecialCells(2)
    '// SpecialCells(2) : 상수가 들어있는 셀
  
    deLimiter = "/"       '//문자 구분자
    For Each rngC In rngTarget
        varTemp = Split(rngC, deLimiter)  '//선택한 셀을 쪼개서 배열에 넣음
        rngC.Offset(, 2).Resize(1, UBound(varTemp) + 1) = varTemp
        '// Resize(RowSize,ColumnSize) : 지정된 범위의 크기를 조정
        '// Rowsize : 새 범위의 행 수를 지정
        '// ColumnSize : 새 범위의 열 수를 지정
        '// Ubound(arrayname, dimension) : 배열에서 지정된 차원의 최대 범위를 Long으로 반환
        '// arrayname : 배열 변수의 이름
        '// dimension 은 생략하면 1차원을 의미
    Next rngC
    
    Set rngTarget = Nothing '// 변수 초기화
    Columns("C:G").AutoFit
End Sub

Cell_Split_VBA.vbs

Cell_Split_VBA.xlsm

위 VBA 코드를 복사하여 Alt + F11 키 누른다음 삽입 --> 모듈 눌러서 나오는 화면에 붙여넣기 하세요.

그리고 매크로 버튼만들기(http://link2me.tistory.com/151) 하고 연결시키면 됩니다.

SpecialCells(2) 의 의미가 뭔지 알고 싶다면 엑셀에서 도움말 F1를 눌러보세요.

Resize 를 해주는 이유는 구분자(delimiter)로 분리되는 배열(varTemp)의 범위가 동적으로 변할 수 있어서 입니다.

Resize(1,n) 이라는 건 행은 1이고 열의 크기는 n 으로 변한다는 것

rngC, rngTarget 개념 설명은 다른 게시물 http://link2me.tistory.com/215 참조하면 되요.

실행결과는

Resize 를 배열변수 고려없이 그냥 상수(숫자)로 직접 지정하면 어떤 변화가 일어나는지 확인해보면 압니다.

[VBA] 연속되는 값의 증가 최대값 모두 구하기

바로 이전 게시물을 버전업하여 작성한 것입니다.

VBA 고수분의 자료를 참조하여 응용하고 재 작성한 것입니다.

자료의 양이 방대할 경우에는 모든 값을 다 구해야 하는 거 아닌가 싶기도 해서 다시 구현해 봤습니다.

가령 구하는 값의 종류가 10개 라고 할 때에도 자동으로 10개의 값에 해당하는 증가 최대값을 모두 자동으로 구하는 식입니다.

Option Explicit

Sub MAX_Duplicate_Cnt()
    Dim rngCh, rngT           '// 입력할 열 글자
    Dim i As Integer   '// 한 셀씩 변하는 변수 지정
    Dim endRow As Integer   '// 최대 Row 수 구하는 변수 지정
    Dim cnt, Max_Cnt As Integer
    Dim oldTime As Single   '// 걸린 시간 구하는 변수 지정
    Dim rngAll As Range     '// D열의 모든 문자를 넣을 변수
    Dim rngC As Range       '// D열의 각셀을 넣을 변수
   
    oldTime = Timer     '// 시간 변수 설정, 필요없으면 콤마(,)로 disable
    Application.ScreenUpdating = False              '화면 업데이트 (일시)정지
   
    rngCh = "A"         '// 검사할 열
    endRow = Cells(Rows.Count, rngCh).End(3).Row
    rngT = "B"          '// 결과를 뿌려줄 열
    cnt = 1             '// 초기 카운트
    Max_Cnt = 1         '// 최대값 구하는 초기값

    '------------------- 연속되는 값 증가 구하는 함수식 --------------------------
    For i = 1 To endRow - 1
        If Cells(i, rngCh).Value = Cells(i + 1, rngCh).Value Then
            Cells(i + 1, rngT).Value = cnt + Cells(i, rngT).Value
        Else
            Cells(i + 1, rngT).Value = 1
        End If
        Cells(i, 4).Value = Cells(i, rngCh).Value   '// A열을 D열에 모두 복사 숫자4는 D열을 의미
    Next i
   
    '------------------- D열의 중복값 모두 제거 및 오름차순 정렬 ----------------------
    Set rngAll = Range("D2", Cells(Rows.Count, "D").End(3))  'D열 데이터영역을 변수에
    For Each rngC In rngAll                               'A열 각셀을 순환
        cnt = WorksheetFunction.CountIf(rngAll, rngC)   '각셀의 countif 값을 변수에
        If cnt > 1 Then                                        '만약 같은 이름이 1개 이상이면
            rngC.Resize(, 1).ClearContents            '그 열의 데이터를 삭제
        End If
     Next rngC

    Range([D2], Cells(Rows.Count, "D").End(3)).Sort key1:=Range("D2"), order1:=xlAscending  '오름차순 정렬
   
    '--------------------- 값의 변수 전체에 대한 연속 최대값 구하기 ----------------------
    Columns("E:E").EntireColumn.ClearContents   '// 값을 산출할 열 전체를 초기화
    For Each rngC In rngAll
        For i = 1 To endRow - 1
            If rngC.Value = Cells(i, rngCh) Then
                If Max_Cnt <= Cells(i + 1, rngT).Value Then     '// 최대값 구하는 조건
                   Max_Cnt = Cells(i + 1, rngT).Value
                   rngC.Offset(0, 1).Value = Max_Cnt
                   rngC.Offset(0, 1).HorizontalAlignment = xlCenter '// 가운데 정렬
                   rngC.HorizontalAlignment = xlCenter
                End If
            End If
        Next i
        Max_Cnt = 1     '// 최대값 초기화
    Next rngC
   
    Set rngAll = Nothing     '개체변수 초기화(메모리 비우기)
    Set rngC = Nothing
 
    'MsgBox "총 " & Format(Timer - oldTime, "#0.00") & " : 초 소요"

End Sub

첨부한 파일을 엑셀파일에서 활용하면 됩니다.

원하는 형태로 약간 수정해서 사용하면 됩니다.

연속중복값계산.vbs

[엑셀] 데이터베이스 함수 DSUM 알아보자

데이터베이스 함수는 앞에 D(Database)를 포함하고 있으니 D가 들어간 함수가 데이터베이스 함수라고 이해하시면 좀 쉽겠네요.

도대체 데이터베이스가 뭐야? 인터넷 검색해봤더니 '특정 조직의 응용업무에 공동으로 사용하기 위하여 운영상 필요한 데이터를 중복을 최소화하여 컴퓨터 기억 장치내에 모아 놓은 집합체' 라고 나오네요.

보통 엑셀에서 다루는 데이터라고 해봐야 많아봐야 몇만 LINE(행) 입니다.

하지만 전문적으로 데이터를 다루는 데이터베이스 툴은 어마어마한 양의 데이터를 쉽고 편하게 다룰 수 있도록 만들어져 있습니다.

공짜로 사용할 수 있는 데이터베이스 툴도 있고 상용 데이터베이스 TOOL도 있습니다.

오라클, MYSQL, MSSQL 등 이런 걸 들어보셨을 겁니다. 이런 TOOL들이 데이터베이스 TOOL입니다.

블로그에 작성하는 글도 모두 데이터베이스에 저장된다고 보면 됩니다.

데이터를 저장할 때 등록한 날짜도 기록하고, 작성자가 누군지도 기록하고, 작성하는 내용이 뭔지도 기록하고요.

테그달기도 기록 등등 구분할 수 있는 걸 각각 구분하는 걸 필드라고 이해하시면 됩니다.

그런 각각의 필드에 기록된 정보중에서 내가 필요로 하는 값만 뽑아내보자....

아래 그림을 보시고 그냥 단순하게 이걸 표라고 이해하지 말고 데이터베이스라고 이해를 하셔야 합니다.

여기서는 행이 몇행 안되지만 이 행이 수만 LINE 이라고 가정해보고 접근해봅시다.

날짜, 공급처, 품목, 단가, 수량, 가격이 바로 필드(Field)라는 구분자 입니다.  

그 아래 2행부터는 실제 데이터가 저장되는 값이구요.

오늘 다룰 DSUM 함수의 의미를 알아보겠습니다.

보통 문제가 이렇게 나옵니다.

공급처가 동아이고 수량이 20개 이상인 가격의 합계를 구하라.

여기서 조건은 뭘까요?

필드명이 공급처이고 데이터는 동아 AND 필드명 수량이고 데이터는 20개 이상인 것이 조건입니다. 

가격의 합계를 구하라 는 조건을 만족하는 가격 필드의 데이터만 합산하라는 뜻입니다.

그럼 그림을 다시 그려서 살펴보겠습니다.

데이터베이스는 표 전체 구간입니다.

필드는 몇번째 열인가? 6번째 열이군요. 그럼 6을 써주면 됩니다. 아니면 직접 필드명에 해당되는 F1 을 직접 써도 되구요. 

조건은 AND 조건도 있고 OR 조건도 있습니다.

조건식을 표현할 때 AND 조건과 OR 조건은  

필드명 이름을 적는 줄과 필드명 아래줄에 구하고자 하는 조건을 적습니다. (필드명은 실제 존재하는 필드명을 써주어야 함) 

이때 구하려는 조건을 같은줄에 적으면 AND 조건이구요. 다른 줄에 적으면 OR 조건입니다.

그럼 DSUM 함수를 써서 AND 조건과 OR 조건을 만족하는 합계를 한번 구해보도록 하겠습니다.

다른 예제를 하나 더 살펴볼까요?

소속이 부산이고 준우승횟수가 5회 이상인 기수들의 우승횟수의 합을 구하라. 

먼저 조건식을 구해야 합니다.

노란색 표시부분이 조건식입니다.

그리고 조건을 만족하는 DSUM 함수를 수식에 맞게 써주면 됩니다. 

데이터베이스 함수에는 DSUM 함수만 있는게 아니고

DAVERAGE 함수는 데이터베이스 조건을 만족하는 평균을 구하는 함수

DCOUNT 함수는 데이터베이스 조건을 만족하는 숫자가 들어있는 셀의 갯수를 구하는 함수 

DCOUNTA 함수는 데이터베이스 조건을 만족하는 숫자/문자가 들어있는 셀의 갯수를 구하는 함수 

DMAX 함수는 데이터베이스 조건을 만족하는 최대값을 구하는 함수 

DMIN  함수는 데이터베이스 조건을 만족하는 최소값을 구하는 함수 

가 있습니다. 

DSUM_sample.xlsx

[VBA] 연속되는 값의 몇번이나 나왔는지의 최대값 구하기

연속되는 값의 최대값 구하는 VBA 수식을 만들어봤습니다.

조건이 없는 최대값을 구하는 함수는 Application.Max 함수를 호출해서 구하면 쉽게 구해집니다.

조건이 있는 경우는 DMax 함수를 사용하면 되는 거 같은데 이건 아직 못해봤구요.

일단 허접하게 구현을 해봤습니다.

Sub MAX_Duplicate_Cnt()
    Dim rngCh, rngT           '// 입력할 열 글자
    Dim i As Integer   '// 한 셀씩 변하는 변수 지정
    Dim endRow As Integer   '// 최대 Row 수 구하는 변수 지정
    Dim Cnt, Max_Cnt1, Max_Cnt2 As Integer
    Dim oldTime As Single   '// 걸린 시간 구하는 변수 지정
   
    oldTime = Timer     '// 시간 변수 설정, 필요없으면 콤마(,)로 disable
    rngCh = "B"         '// 검사할 열
    endRow = Cells(Rows.Count, rngCh).End(3).Row
    rngT = "C"          '// 결과를 뿌려줄 열
    Cnt = 1             '// 초기 카운트
    Max_Cnt = 1

    For i = 1 To endRow - 1
        If Cells(i, rngCh).Value = Cells(i + 1, rngCh).Value Then
            Cells(i + 1, rngT).Value = Cnt + Cells(i, rngT).Value
           
            '// 아래는 같은 값을 가진 연속된 개수의 최대값을 구하는 수
            If Cells(i + 1, rngCh).Value = "O" Then
                If Max_Cnt1 < Cells(i + 1, rngT).Value Then
                    Max_Cnt1 = Cells(i + 1, rngT).Value
                End If
            Else
                If Max_Cnt2 < Cells(i + 1, rngT).Value Then
                    Max_Cnt2 = Cells(i + 1, rngT).Value
                End If
            End If
           
        Else
            Cells(i + 1, rngT).Value = 1
           
        End If
    Next i
   
    Cells(8, "F").Value = Max_Cnt1
    Cells(9, "F").Value = Max_Cnt2
 
    MsgBox "총 " & Format(Timer - oldTime, "#0.00") & " : 초 소요"

End Sub

처리결과 화면은

하지만 구해야 할 값이 딱 2개만 있는 경우가 아니라면 최대값은 수식으로 넣도록 하고 필터를 걸어서 확인하는 방법이 더 좋을 수도 있다고 봅니다.

서로 다른 모든 변수들을 자동으로 찾아서 계산한다음에 넣는 방법은 아직 모르겠네요..

첨부파일은

연속중복검사.xlsm

자체셀내의 중복여부 검사

엑셀 데이터가 많을 경우 기준열(Column)에 반복된 값이 얼마나 들어 있는지 검사하는 VBA 코드이다.

어떻게 처리되는지 확인은 F8키를 눌러서 순차적으로 실행되는 것을 확인하면 된다.

중복검사할 때 엑셀함수 CountIF 를 사용하듯이, VBA 에서도 엑셀에서 기본 제공하는 countif 함수를 이용할 수 있다. 이용하는 방법은 application.countif(범위,조건) 을 주면 된다.

rngC.Offset(0, rngT) = "중복" 와 같이 offset(행,열) 을 이용하기도 하지만,

Cells(행,열) 사용하면 직관적으로 이해하기 쉽다. Cells(rngC.Row,"D") = "중복" 이라고 하면 D열에 표시가 되는구나 하고 이해할 수 있다.

Sub 자체셀내의중복검사()
    Dim rngCh           '// 입력할 열 글자
    Dim rngC    As Range   '// 한 행(Row)씩 변하는 변수 지정
    Dim rngTarget As Range '// 대상 범위 지정변수
    Dim sRow As Integer '// 열을 직접 지정하지 않고 ActiveCell 기준으로 정하고자 할 때
    Dim rngT As Integer     '// 결과값을 표시할 위치 지정할 변수
    Dim oldTime As Single
       
    Application.ScreenUpdating = False  '//화면 업데이트 일시 정지, 이걸 지정해줘야 매우 빠르게 처리함

    oldTime = Timer
       
    rngCol = "C"   
    Set rngTarget = Range(Cells(2, rngCol), Cells(Rows.Count, rngCol).End(3))
    '// End(3) 은 End(xlUp) 으로 엑셀이 제공하는 총 행의 수로부터 위로 이동하라는 명령
    '// End(3)(2) 는 값이 있는 마지막행의 offset(1,0) 과 같은 의미로 바로 아래 행을 의미
    
    'ActiveCell.Select  '// ActiveCell 있는 셀 기준으로 중복검사를 할 경우
    'Set rngTarget = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(3))
   
    rngT = 3  '// 검사하는 열로부터 몇번째 열에 중복이라는 글자를 표시할 것인가?
  
    For Each rngC In rngTarget
        rngC.Offset(0, rngT).ClearContents
        rngC.Font.Bold = False
        rngC.Offset(0, rngT + 1).ClearContents
       
        If Application.CountIf(rngTarget, rngC.Value) > 1 Then   '// 중복 개수가 2개 이상인 것만
            rngC.Font.Bold = True
            rngC.Offset(0, rngT) = "중복"
            rngC.Offset(0, rngT + 1) = Application.CountIf(rngTarget, rngC.Value)
        End If
    Next rngC
    
    Set rngTarget = Nothing     '// 변수 초기화
    MsgBox "총 " & Format(Timer - oldTime, "#0.00") & " : 초 소요"

End Sub

Countif_VBA-01.vbs

자료가 방대한 경우 어느정도 시간이 걸리는지 체크해보려고 넣었다

첨부파일은 텍스트파일이므로 수정해서 사용하시면 됩니다.

[엑셀] Subtotal 함수

Subtotal 함수는 자동필터와 같이 사용하면 유용한 함수 입니다.

피벗테이블 다루는 걸 알면 굳이 Subtotal 함수를 사용하지 않아도 유용하게 원하는 걸 얻을 수도 있습니다.

숨겨진 값 무시하는 건 딱 100 숫자가 더 많아요..

하지만 외울 필요 전혀 없어요...  

함수사용은 원하는 필드에 =SUBTOTAL( 를 치고나면 옵션을 선택하는 창이 자동으로 떠요.

그런다음에 원하는 구간 마우스로 지정하면 설정됩니다.

함수사용은

subtotal(인수,조건범위)

아래 그림처럼 자동필터를 지정해놓은 상태에서 SUM 을 한 값과 SUBTOTAL 함수를 지정하여 합계를 지정한 값을 비교해 보겠습니다.

품목이든, 날짜든, 공급처든 원하는 걸 눌러서 선택하면 되는데요.

여기서는 그림 자르기 편하게 그냥 품목으로 설명하겠습니다.

아래 2번 항목에서 독서대만 선택하겠습니다.

독서대만 선택했더니 .....

행번호가 1, 2, 5, 12, 16 번호가 보이는군요.

즉 필요한 번호만 보이는 것입니다.

그리고 SUBTOTAL 함수를 적용한 값은 변경된 것이 보이시죠?

육안으로 가격을 더해서 계산해도 같은 값인 걸 알 수 있을 겁니다.

하지만 전체 적용된 SUM 의 값은 변동이 없습니다.

이처럼 원하는 부분항목의 값을 구하고자 할 때 유용하게 사용할 수 있는게 SUBTOTAL 함수입니다.

좀더 그림을 통해서 공급처도 변경해보고 날짜도 변경해보겠습니다.

위 그림을 보시면 공급처에도 필터가 적용되었고, 품목에도 필터가 적용되었습니다.

SUMIFS 함수처럼 두개 이상의 조건을 만족하는 값을 구하고자 하는 것도 쉽게 값을 구할 수 있습니다.

5월에 동아에서 공급한 것의 가격을 구해보겠습니다.

필더를 날짜, 공급처를 각각 원하는 것만 체크해주면 됩니다.

SUM 과 SUBTOTAL 함수를 같이 놓고 비교를 해봤는데 이해가 쉽게 되었는지 모르겠네요..

숨겨진 값 무시와 포함이 제대로 안된다는 분이 계셔서 그 부분도 그림으로 포함합니다.

합계를 구하는데 숨겨진 값은 무시하라는 조건을 하려면 9 가 아닌 109로 변경을 하면 됩니다.

값을 9로도 해보고 109로도 해보면 어떤 변화가 발생하는지 확인이 됩니다.

합계뿐만 아니라 다른 것도 유용하게 사용할 수 있는게 옵션 조정을 할 수 있도록 만든게 SUBTOTAL 함수 이므로 알아두면 여러모로 편리합니다.

Subtotal_sample.xlsx

첨부파일 첨부하니 연습해보실 분은 직접 해보세요..

[VBA응용] VLookup VBA 이용한 예제 1

네이버 지식IN 사이트에 올라온 것이 있어서 이것을 한번 구현해 봤습니다.

질문 : 이곳 눌러보세요

날짜 값이 0 인 경우에 1900-01-00 으로 표시가 되는데 이걸 가져오면서 아예 안가져왔으면 좋겠다고 하는 내용이라 상황에 맞게 구현을 해봤습니다.

아래 그림을 보시면 테이블 구간 설정만 하고 몇번째 열의 값을 가져올 것인지, 어디에 뿌려줄 것인지만 지정하면 간단하게 끝나도록 구현된 VBA 코드입니다.

VLookup_VBA-01.vbs

지식인샘플_VLOOKUP_VBA.xlsm

VLOOKUP_VBA_modify.xlsm

파일이 필요하신 분은 매크로가 들어있는 엑셀파일을 다운로드 받으면 되구요.

그냥 VBA 소스가 필요하신 분은 위의 vbs 파일을 받으면 됩니다.

세세한 VBA 함수 등을 알고 싶으면 http://rosa0189.blog.me/ 사이트 가셔서 검색창에서 검색해서 원하는 VBA 함수나 기본지식을 같이 활용하시면 좋습니다.

IF문의 조건문을 충족하는가? 충족하지 않는가에 따라 다른 결과를 보여줍니다.

[VBA기초] 아스키코드 알아내기

해당 글자의 아스키코드 값이 어떻게 되는지를 알면 응용하여 구현할 때 편할거 같아서 포스팅 합니다.

글자를 입력하고 나서 매크로 단축키를 누르면 해당 아스키코드 값을 찾아주게 해봤습니다.

대문자 A가 65, Z가 90 이군요.

소문자 a가 97, z는 122 이군요.

숫자는 48 ~ 57까지 이군요. 문자열에서 숫자만 추출하도록 하려면 해당글자의 아스키코드로 변환한다음 하나 하나 비교하면서 해당숫자만 찾아내도록 하면 됩니다.

한글은 값을 추출해보니 전부 0보다 작더라구요.

아스키 코드 추출하는 함수가 Asc(글자) 라는 것만 알면 나머지는 쉽죠.

이번에는 아스키코드 숫자를 넣고 해당 글자를 찾아내는 걸 해봤습니다.

스페이스바는 아스키코드가 32 입니다.

해당 소스코드가 위와 같으니 직접 한번 해보시면 금방 이해하실 수 있습니다.

[VBA기초] DO LOOP 구문

오늘 알아볼 엑셀 기본구문은 DO LOOP 문입니다.

DO LOOP 문은 처리할 횟수가 지정되어 있지 않으므로 조건을 잘못 설정하면 무한 반복처리될 수 있습니다.

무한 루프에 빠지지 않도록 주의해야 하며, 만약 무한 루프에 빠지면 CTRL + BREAK 키로 중단시킬 수 있습니다.

그럼 예제 하나를 살펴볼까요?

변수 i 는 1부터 시작하여 최대 10이 되기전에 끝나겠군요.

그런데 i 가 7이면 Do Loop 문을 빠져나가라고 되어 있으니 i 는 9까지는 가보지도 못하고 끝나겠는데요.

지금까지 나온 기본구문 For Next 구문, For Each Next 구문, Do Loop 구문 등을 상황에 맞게 적절하게 사용하면서 조건 IF문을 잘 활용하면 원하는 결과를 얻는데 쉽게 얻을 수 있을 겁니다.

기본적인 엑셀함수 기능에 대해서 좀 더 알면 자유자재로 활용할 날이 올거라 믿으며 오늘은 이만 ^^

[VBA기초] 공백제거

엑셀을 다루다보면 첫번째 셀만 공백을 제거하고 싶을 때가 있습니다.

보통은 Trim 함수를 사용하면 제거가 되는데요.

위 엑셀은 Trim 함수를 써도 첫번째 공백이 제거가 되지 않네요.

시스템에서 가져온 데이터들이 보통 이런 경우들이 많은데요.

이런 경우에는 어떻게 해결할까요?

바로 아스키코드 값을 알아낸 다음에 셀의 첫번째 문자열의 값을 알아내는 겁니다.

Sub 첫문자열공백제거()
    Dim rngTarget As Range
    Dim rngC As Range
    Dim sName  As String
   
    ActiveCell.Select     '// 현재 커서가 있을 셀 선택
    Set rngTarget = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(3))
   
    For Each rngC In rngTarget
        sName = Left(rngC.Value, 1)     '// 첫번째 문자열 추출
        'MsgBox Asc(sName)              '//첫공백의 아스키 값을 알아내고자 한다면...
        If Asc(sName) = 63  Or Asc(sName) = 32 Then   '//스페이스바의 아스키 값은 32
            rngC = Mid(rngC, 2, Len(rngC))
        ElseIf Asc(sName) < 65 Or Asc(sName) > 90 Then    '// 첫글자가 영문자 대문자가 아닌지 검사
            rngC = UCase(sName) & Right(rngC, Len(rngC) - 1)
            '// 첫글자를 영문대문자로 변경 처리
        End If
    Next
   
End Sub

공백제거.vbs

위 VBA 함수를 이용하여 MsgBox Asc(sName) 를 활성화하여 공백의 값을 알아냅니다.

스페이스바의 아스키 값은 32 이더군요. (http://link2me.tistory.com/222 게시물 참조)

그런 다음에 'MsgBox Asc(sName) 로 처리하고 나서 F5 를 누르면 됩니다.

아니면 단축키 매크로 만들면 되구요.

첫 문자열 공백제거를 위한 단축키를 만들어서 누르면 ActiveCell 이 있는 열 전체가 모두 공백이 제거 됩니다.

공백의 아스키 값을 알아낼 수 없었다면 힘들게 하나 하나 지우는 고생을 했을 수도 있고

강제로 Mid함수를 이용하여 해당 행만 업데이트 할 수 있습니다.

For Each ~ Next 구문

For Each ~ Next 구문은 For ~ Next 구문과 거의 동일한데 약간 다른 점은 ...

For ~ Next 구문은 전체중에서 원하는 것만큼만 실행이 가능한데

For Each ~ Next 구문은 무조건 전체를 실행하며, 거꾸로 순서를 지정해서 할 수가 없습니다.

이거 무슨 뜬구름 잡는건지 감이 잘 안잡히시죠?

Selection 한 구간 전체는 배열이구요. 각각의 Cell은 배열의 요소 입니다.

Sub Selection_VBA()

Dim rngC As Range

For Each rngC in Selection

if rngC.Value >0 Then 실행문

Next rngC

End Sub

셀의 범위 구간을 지정하여 구간내에서 실행문을 실행하여 원하는 결과를 얻을 수 있습니다.

위의 반복문은 셀의 총 갯수가 11개 이므로 11번 반복해서 돌아가고 IF문 조건을 만족하는 결과를 산출합니다.

Range는 구간을 직접 지정할 수도 있고 Selection 으로 구간을 그때 그때 정할 수도 있습니다.

보통은 구간범위를 지정하여 반복수행하여 결과를 얻어냅니다.

Sub test()
    Dim rngC, rngAll As Range
   
    Application.ScreenUpdating = False
    Set rngAll = Range([A2], Cells(Rows.Count, "A").End(3))
    For Each rngC In rngAll
        '// 처리하고자 하는 내용을 IF문과 함께 처리
    Next rngC
    Set rngAll = Nothing    '// 메모리 비우기
    Application.ScreenUpdating = True
    MsgBox "처리완료"
End Sub

요소 rngC를 지정하는 것은 rngC 라고 하고, 어떤 사람은 C 라고 하고, rng 라고 하기도 하는 등 사람의 취향에 따라 다릅니다.

위 그림처럼 Worksheets 를 대상으로 처리하는 실행문을 구현하여 원하는 결과를 얻을 수도 있습니다.

Sub ShowName()
    Dim sht As Worksheet
    For Each sht In Worksheets
        MsgBox sht.Name, , "워크시트 이름"
    Next ws
End Sub

대략 어떻게 돌아가는 구나 정도 이해가 되었다면 예제 등을 통해서 자주 접하는 것이 원하는 걸 얻을 수 있는 지름길 입니다.

VBA 입문 과정에서는 고수들이 만든 VBA 코드를 복사하여 붙여넣기 한 다음에

F8키를 눌러가면서 의미를 파악하면서 하나 하나 내것으로 만들어가는 겁니다.

[VBA 기초] FOR NEXT 구문

IF문과 함께 가장 많이 사용하는게 FOR NEXT 구문입니다.

단순 반복해야 하는 상황을 아주 간단하게 해결해주는 게 바로 FOR NEXT 구문입니다.

FOR NEXT 구문은 위 2가지 입니다. STEP 증가값을 생략하면 1씩 증가됩니다.

시작값부터 종료값까지 증가되고 나면 FOR 제어문을 빠져나오게 됩니다.

1000개의 셀에다가 값을 기록해야 하는 경우라면

변수는 통상적으로 I 를 많이 사용하니까

Sub for_next_basic()

Dim r As Integer

For r = 1 to 1000

Cells(r, 1).Value = 3 * r

Next r

Msgbox "처리완료"

End Sub

이런식으로 원하는 셀을 고려하고 값을 계산하도록 만들거나 원하는 셀에다가 반복적으로 값을 기록하게 합니다.

셀을 중앙정렬하고 싶은데 중앙정렬 시키는 값을 잘 모르겠다??

저같은 경우에는 엑셀 자동매크로를 실행해보고 중앙정렬 하도록 하는 값이 뭔지 찾아낸 다음에 그걸 찾아서 적용합니다.

F1 도움말 키를 누른다음에 '중앙정렬 VBA' 라고 입력을 해보는 것도 방법입니다.

기본 함수 IF문

VBA 에서 다루는 기본적인 개념을 이해하고 나서 간단한 함수 다루는 개념을 배우면 됩니다.

엑셀에는 기본적으로 비주얼베이직 6.0이 들어있다고 합니다.

그래서 간단하게 생각하는 로직을 구현하면 엑셀과 연동하여 원하는 결과를 얻을 수 있게 됩니다.

VBA 를 다루기 위한 VB편집기 창을 띄우는 방법이었구요.

엑셀함수에서 다루는 IF문을 VBA에서도 다루고, 반복처리를 위한 FOR 문, DO WHILE 문에 대한 기본적인 개념을 알고 처리를 합니다. 엑셀함수에서는 직접 셀에 대한 걸 비교하여 원하는 결과를 얻는데 VBA 에서는 변수선언과 더불어 약간 더 로직 다루는 부분이 더 들어간다고 이해하시면 될 거 같습니다. 저도 아직은 초보라서 아는게 부족합니다.

초보자 입장에서 최대한 쉽게 설명하고픈 마음만 있는 셈이죠 ^^

아래는 조건식을 비교할 경우에 사용하는 비교 연산자와 논리 연산자의 의미입니다.

엑셀 함수에서 가장 많이 다루는 IF 조건문이 아래 그림처럼 되어 있죠.

VBA 에서는 IF 조건식 THEN 형식으로 되어 있습니다.

간단한 예제를 하나 살펴보겠습니다

VBA 에서 가장 기본적으로 다루는 IF 문(조건문)에 대해서 알아봤습니다.

이 IF문과 셀을 다루는 개념이 결합되어 원하는 결과를 얻어내는 겁니다.

반복적으로 처리해야 할 것은 FOR문이나 DO WHILE 문과 결합되어 원하는 결과를 도출하구요.

다음에는 FOR 문에 대해서 알아보겠습니다. ^^

[VBA 기초] Selection 의 개념잡기

엑셀은 셀을 다루는 것이므로 셀에 대한 범위 지정 등을 잘 알아야 합니다.

마우스로 선택한 구간에서 구하고자 하는 값을 알기 위한 사항입니다.

Sub CountRows()
    Dim rngCnt As Integer
    Dim sRow As Integer
    Dim eRow As Integer
       
    Dim rngEnd As Range
   
    rngCnt = Selection.Rows.Count  '// 선택된 영역의 총 Row 수
    sRow = Selection.Row  '// 선택된 영역의 첫번째 행의 위치 반환
    eRow = Selection.Row + Selection.Rows.Count - 1  '// 선택된 영역의 마지막 행 위치 반환
   
    MsgBox "첫번째 행은 " & sRow
    MsgBox "마지막 행은 " & eRow
    MsgBox "전체 선택한 행의 수는 " & rngCnt
   
End Sub

그러면 이제 아래 내용이 무슨 의미를 알아봅시다

Dim h As Long, r As Long
r = Cells(Rows.Count, "B").End(xlUp).Row

값이 들어 있는 B열의 마지막셀을 찾아서 Row(행)의 값을 r 에 넣어라

'// Cells(Rows.Count, "B") 는 B열의 마지막 셀, 엑셀이 제공하는 마지막셀을 의미

'// Cells(Rows.Count, "B").End(xlUp) 은 Cells(Rows.Count, "B").End(3) 으로도 표기하며

'// B열의 마지막셀로부터 위(xlUp) 으로 올라와서 마지막 데이터가 있는 셀을 의미

n = Cells(Rows.Count, "B").End(xlUp).Column

값이 들어 있는 B열의 마지막셀을 찾아서 Column(열)의 값을 n 에 넣어라

Range("A10:B" & r) 의 의미는

A10 셀부터 B열의 마지막 값이 들어있는 셀까지의 범위

Range("A10:B" & Cells(Rows.Count,"B").End(3).Row)

또는 Range([A10], Cells(Rows.Count, "B").End(3)) 으로도 많이 사용합니다.

현재 커서가 있는 셀은 ActiveCell 입니다.

현재 커서가 있는 행 전체를 선택하는 것은 ActiveCell.EntireRow.Select 입니다.

앞에서부터 순차적으로 ActivCell 을 적어주고 전체 행인 EntireRow 를 적고 마지막으로 선택(Select)를 하면 됩니다.

어떤 의미인지 조금이나마 감이 잡히시나요?

댓글과 공감은 큰 힘이 됩니다

[VBA 기초] Range, Cell 의 개념 알아보자

제가 멋모르고 공개해준 VBA를 가져다가 응용하려고 할 때 가장 막힌 부분이 셀에 대한 개념이 부족해서였습니다

물론 좋은 강좌도 많지만 개념 이해하는데 턱턱 막히는 원인을 조금이라도 해소해보고자 부족한 실력이지만 적어봅니다.

먼저 참고할만한 게시물 참조 걸게요 권현욱 Range강좌

엑셀은 셀을 다루는 프로그램입니다.

현재 커서가 위치한 셀을 ActiveCell 이라고 합니다. 이 셀이 위치한 곳을 표현하는 방법에 대해 먼저 알아보겠습니다.

Cells(행,열) 로 표현하는 데

위의 ActiveCell 의 위치를 보면 A열이고 행으로 보면 2번째 행이죠?

그럼 Cells(2,"A") 라고 표현할 수 있습니다. 또 다르게 Cells(2,1) 이라고 표현할 수도 있습니다.

Range("A2") 라고도 표현하기도 합니다.

왜 이런 시시콜콜한 걸 얘기하나면 이런 시시콜콜한 표현방법을 모르면 응용할 때 막히더라구요.

A2 셀에다가 값을 100을 집어넣어라 라는 걸 VBA로 구현하라 라고 한다면....

엑셀 띄워진 상태에서 Alt + F11 키 누르고 Visual Basic for Application 창에서 삽입 -> 모듈 눌러주세요

Sub Value_Add()
    Range("A2").Value = 100
End Sub
를 적어주고 나서 F5키를 눌러보세요 ^^

그러면 위 그림처럼 값이 기록되는 걸 볼 수 있어요.

이번에는 셀 하나만 선택하는게 아니라 여러 셀을 선택해 봅시다.

Range("A2","C5").Value = 100 이라고 수정해서 넣어보세요

그림을 보니까 A2 에서부터 C5 영역까지 모두 100 으로 채워졌군요.

이번에는 Range("A2","C5").Value = 100 대신에 Range(Cells(2,"A"), Cells(5,"C")).Value = 200 이라고 바꿔보세요.

Sub Value_Add()
    'Range("A2", "C5").Value = 100
    Range(Cells(2, "A"), Cells(5, "C")).Value = 200
End Sub

이번에는 아래 그림을 보면 값이 좀 더 많이 들어 있죠?

현재 커서가 위치한 셀이 A2 이군요. 이번에는 Range("A2").CurrentRegion.Select 라고 입력합니다.

Sub Value_Add()
    Range("A2").CurrentRegion.Select
End Sub

A2와 인접해 있는 주변셀들이 모두 선택되었군요.

여기서 D6 셀에다가 값을 400 이라고 적어보고 나서 다시 F5로 실행을 해보겠습니다.

이번에는 인접영역이 훨씬 더 넓게 선택되었군요.

아래 그림을 보면 현재 커서가 있는 셀이 A2 이죠?

육안으로 보면 마지막 행과 마지막 열이 어디인지 알 수 있습니다.

VBA 다루는 건 이렇게 육안으로 보이는 거 처리하려고 하는게 아니라 행(Row)이 5000 행이고 열(Column)이 10 정도 되는 것일 수도 있습니다. 이런 영역안에서 값을 찾아서 처리하려고 한다면 행 하단까지 전부 찾아서 기록하고, 열도 찾아서 기록하고 이런 짓을 하면 정말 힘들겠죠... 선택영역 범위를 자동으로 선택(Select)하는 방법은 없을까요?

지금까지 죽 다룬 Range 와 Cells 의 개념을 잘 조합하면 됩니다.

Range(Cells(~,~),Cells(~,~)) 라는 거 앞에서 언급한거 보셨지요?

Range 안에 앞에는 Start 지점, 뒤에는 End 지점 이라는 거 감이 잡히신다면...

Cells(~,~) 의 Cells(행,열) 을 어떻게 구해서 어떻게 표현해줄 것인가를 알면 된다 이겁니다.

Range(ActiveCell, Cells(~,~)) 라고 적을 수 있습니다. 아니면 직접 Range("A2", Cells(~,~)) 라고 해도 되구요.

그럼 여기서 Cells(행,열) 에 들어갈 마지막 행을 표기하는 방법, 마지막 열을 표기하는 방법을 알면 됩니다

Sub Value_Add()
    Range(ActiveCell, Cells(Rows.Count, "G").End(3)).Select
End Sub

G열은 육안으로 보이니까 직접 적어주는게 쉽고 편합니다.

Cells(Rows.Count, "G").End(3) 의 의미가 뭔지 알아야겠죠?

제가 열심히 설명하는 것보다는 http://rosa0189.blog.me/60145634878 게시글을 보는게 더 이해가 잘 되실 겁니다.

Cells(Rows.Count, "A") 의 의미는 A열의 마지막 행의 위치입니다.

엑셀이 제공하는 마지막 행은 엑셀 2003은 65,536 행(Row)이 있고, 2007 버전 이후로는 1,048,576 개의 행(Row) 입니다.

엑셀이 제공하는 최대 행까지 내려가라. 그런 다음에 End(3)의 의미는 End(xlUp) 으로 위로 이동하여 값이 있는 마지막 행의 위치를 의미합니다.

Range(Range("A2").End(xlDown), Range("A2")).Select 을 실행해보세요

Range(Range("A2"), Range("A2").End(xlDown)).Select 범위 지정을 이렇게 해도 결과는 동일합니다.

보통은 Range([A2], Cells(Rows.Count, "A").End(3)).Select 라는 걸 많이 사용합니다.

Range([A2], [A2].End(4)).Select 도 표현도 가능합니다.

결과는 A11 까지 선택되지 않고 중간에 공백이 있는 곳에 멈춥니다. 그러므로 범위를 어떻게 지정하느냐에 따라 결과가 달라질 수 있다는 점 알아두셔야 합니다.

여기서 중요한 거 한가지 짚고 넘어가겠습니다.

엑셀은 마침표(.)를 기준으로 앞에서 부터 뒤로 의미를 부여하여 실행된다는 겁니다.

Range("A2").CurrentRegion.Select

이 의미는 먼저 Range("A2")를 지정하고 그 다음에 CurrentRegion 을 하고 그 다음에 Select 하라는 겁니다.

Range("A2:G11").Cells(3,2).Value = 7777

이라고 한다면....

그림에서 먼저 Range("A2:G11")을 선택하고 나서 이 영역내에서 Cells(3,2) 라는 의미는 3행, 2열을 선택하는 겁니다.

아래로 3행, 우측으로 2열 그리고 나서 Value 를 7777 을 기록하라는 겁니다. 이해되시죠?

이번에는 여기에 글씨도 굵게 표시하고 폰트 크기도 14로 표기하라고 하고 싶어서 아래처럼 했습니다.

Sub Value_Add()
    Range("A2:G11").Cells(3, 2).Value = 7777
    Range("A2:G11").Cells(3, 2).Font.Bold = True
    Range("A2:G11").Cells(3, 2).Font.Size = 14
End Sub

결과는 잘 나왔습니다.

그런데 보니까 Range("A2:G11")을 반복하면서 쓰고 있네요.

좀 깔끔하게 정리하면 좋겠는데 말이죠..

Sub Value_Add()
    With Range("A2:G11")
        .Cells(3, 2).Value = 7777
        .Cells(3, 2).Font.Bold = True
        .Cells(3, 2).Font.Size = 14
    End With
End Sub

이렇게 With 에 Range("A2:G11") 을 선언하고 처리해줘도 결과는 동일하게 나옵니다.

좀 더 정리를 하면

Sub Value_Add()
    With Range("A2:G11").Cells(3, 2)
        .Value = 7777
        .Font.Bold = True
        .Font.Size = 14
    End With
End Sub

여기서 왜 콤마(.)가 들어간 건지는 이제 이해되시나요?

부족한 실력에 제가 Cell 개념 설명을 한다고 했는데 도움이 좀 되셨는지 모르겠군요. 제가 몸소 겪은 애로사항을 정리한다고 정리한 거라 조금은 도움이 되셨을 거라 믿습니다.

이런 메서드와 연결되어 어떻게 처리되는지 감이 잡히신다면 실력향상은 금방 되실 겁니다.

아래를 실행하면 어떤 결과가 나올까요?  각자 알아서 실행해보시기 바랍니다.

Sub Value_Add()
    Range("A2").CurrentRegion.ClearContents
End Sub

If Left(Cells(i, "a"), 2) = Left(Cells(ActiveCell.Row, "a"), 2) Then '// 셀의 앞 두자가 일치하면

셀을 비교하여 원하는 결과를 얻고자 할 때 앞 두셀만 비교해야 할 경우가 생기죠.

셀의 마지막열의 값을 구하고자 하는 경우에는

lastCol = Cells(1, Columns.Count).End(1).Column

으로 하면 숫자로 표기됩니다.

마지막 열은 요긴하게 사용될 수 있습니다.

Range("C4:M" & Cells(Rows.Count, 3).End(xlUp).Row)

Set rngDB = Worksheets("B").Range("B2", Worksheets("B").Cells(Rows.Count, "B").End(3))
Set rngAll = Worksheets("A").Range("A2", Worksheets("A").Cells(Rows.Count, "A").End(3))

도움이 되었다면 댓글이나 공감 꾸욱 부탁드립니다.