엑셀 VBA #35 / 고급필터 [VBA]

1. 구문
영역.AdvancedFilter xlFilterCopy, 조건범위, 복사위치

2. 활용예제

Dim rng As Range, c As Range, d As Range

Set rng = Range("A1").CurrentRegion
Set c = Range("E1:E2")   '조건범위
Set d = Range("A25")     '복사위치(고급필터링된 값이 뿌려질 첫번째영역)

rng.AdvancedFilter xlFilterCopy, c, d       'xlFilterCopy : 다른 장소에 복사

Why??????

고급필터의 조건식을 엑셀워크시트에 설정하는 걸까.....

VBA코딩 창에 입력불가능한가......

https://www.youtube.com/watch?v=UEf5TCAkLnE&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=35

엑셀 VBA #34 / 사용자정의 함수 [VBA]

1. 목적 : VBA코딩시 자주 사용되는 수식을 함수화하여 편이성 도모
2. 구조
    Function 함수명(인수1,인수2,....) As 자료형
       ~~~~~
    End Function
함수명 : 워크시트함수와 동일하면 안됨
자료형 예시 : Integer, Long,....

3. 활용
'엑셀 VBA #32 / 자동필터_1 [VBA]'에서 사용한 'rng.SpecialCells(xlCellTypeVisible).Count'을 사용자정의함수로 만들기
 
4. 코드
Dim sh1 As Worksheet
Dim rng As Range

Set sh1 = Sheets("자동필터")
Set rng = Range("A1").CurrentRegion

If sh1.AutoFilterMode = False Then rng.AutoFilter
If sh1.FilterMode = True Then sh1.ShowAllData

rng.AutoFilter 2, Range("E2")

Range("A20").CurrentRegion.Clear
If Fcnt(rng) = 3 Then                            '사용자정의함수 적용
    MsgBox "해당되는 조건의 데이타가 없음"
    Exit Sub
End If

rng.SpecialCells(xlCellTypeVisible).Copy Range("A20")
End Sub

Function Fcnt(rng As Range) As Long
    Fcnt = rng.SpecialCells(xlCellTypeVisible).Count     
End Function

https://www.youtube.com/watch?v=sVEJnYuYvQE&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=34

엑셀 VBA #33 / 자동필터_2 [VBA]

앞선 강좌(엑셀 VBA #32 / 자동필터_1 [VBA])의 예제 코드를 실무 사용시 문제점
문제점1.  새로운 조건을 필터시(그 결과개수2개) 이전에 자동필터된 자료(결과개수6개)가 A20셀 아래에 그대로 남아 있어 문제 소지가 있음
문제점2.  아티스트명이 rng영역에 없는 경우, 사용자가 프로그램 오류인지 조건설정 잘못인지 신속 파악힘듬  


새로운 코드
Dim sh1 As Worksheet
Dim rng As Range

Set sh1 = Sheets("자동필터")
Set rng = Range("A1").CurrentRegion

If sh1.AutoFilterMode = False Then rng.AutoFilter
If sh1.FilterMode = True Then sh1.ShowAllData

rng.AutoFilter 2, Range("E2")
Range("A20").CurrentRegion.Clear                                  '문제점1 해결코드
If rng.SpecialCells(xlCellTypeVisible).Count = 3 Then      '문제점2 해결코드
    MsgBox "해당되는 조건의 데이타가 없음"
    Exit Sub
End If

rng.SpecialCells(xlCellTypeVisible).Copy Range("A20")

https://www.youtube.com/watch?v=E0jcmptYAdQ&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=33

엑셀 VBA #32 / 자동필터_1 [VBA]

Dim sh1 As Worksheet, rng As Range

Set sh1 = Sheets("자동필터")
Set rng = Range("A1").CurrentRegion

If sh1.AutoFilterMode = False Then rng.AutoFilter
    '만약 sh1 시트에 자동필터모드가 아니라면(false), rng영역에 자동필터해라
If sh1.FilterMode = True Then sh1.ShowAllData
    '만약 sh1시트에 필터링 되었다면(즉 필터링 데이타가 보여진다면), sh1시트의 모든 데이타를 보이게 해라.
    '구문 'FilterMode=True'에서 True는 생략 가능함. 즉 If sh1.FilterMode then sh1.ShowAllData로 해도 됨

rng.AutoFilter 2, Range("E2")
    'rng영역에 자동필터해라, 자동필터 기준은 rng영역의 2번째이며 조건은 E2셀이다.
rng.SpecialCells(xlCellTypeVisible).Copy Range("A20")
    'rng영역에서 보여지는 셀들(Visible SpecialCells) A20셀에 복사해라

https://www.youtube.com/watch?v=GqDstYjUv00&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=32

엑셀 VBA #09 / 셀 범위 선택하기_4 [VBA] - SpecialCells

엑셀 VBA #31 / 정렬_2 [VBA] - 예제

1. 예제1 : 생년 기준으로 오름차순 정렬

  Dim rng As Range

  Set rng = Range("A1").CurrentRegion
  Set rng = rng.Offset(, 1).Resize(, rng.Columns.Count - 1)
    ' rng변수이름 동일 적용하여, 순차적 적용 !!!!!!자세히 볼것, 
    'Offset(, 1) =offset(0,1)
    '***Resize(, rng.Columns.Count - 1) = =Resize(rng.Rows.Count, rng.Columns.Count - 1) 

        =Resize(3, rng.Columns.Count - 1)

  rng.Sort rng(3, 1), xlAscending, Orientation:=xlSortRows

2. 예제2

For Each c In Range("A1:A7")  'c는 A1 or A2 or......A7
    Set rng = Range(c.Offset(0, 1), c.End(xlToRight)) 'a1에서부터 a1으로부터 셀값이 있는 오른쪽 셀까지
    If rng.Count > 1 Then      'rng 영역 셀 갯수가 1보다 크면 아래 구문 적용
        rng.Sort rng(1, 1), xlDescending, Orientation:=xlSortRows
    End If        'rng 영역 셀 갯수가 1보다 작으면 위 구문 미적용
Next

Offset

- 구조 : 기준셀. Offset(행,열)
- 의미 : 기준셀에서부터 주어진 행렬 만큼 이동하기

Resize
구조 : 기준개체.Resize(행,열)
의미 : 기준개체를 바탕으로 행과 열만큼 영역을 재설정

     !!!!! rng.Resize(,3).Select  = rng.Resize(rng.Rows.Count,3).Select 

           Resize(,3)의 의미는 Resize(0,3)가 아니라~~~

https://www.youtube.com/watch?v=y52qcbqjpJI&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=31

엑셀 VBA #30 / 정렬_1 [VBA]

1. 코딩 예시
  1.1. 정렬기준 : 일자 오름차순
    Dim rng As Range
    Set rng = Range("a4", Cells(Rows.Count, "d").End(xlUp))

    Sheets("정렬1").Sort.SortFields.Clear
    '시트 '정렬1'에 사용자정의목록으로 정렬되는게 있다면 지워라(정렬 코딩시 필수 문장).

    rng.Sort rng(1, 1)
    'rng영역의 첫 번째 행 & 첫 번째 열 기준으로 정렬하라(추가 옵션 없는 경우에는 오름차순임)
 

  1.2. 정렬기준 : 일자 내림차순 & 지역 오름차순
    Dim rng As Range
    Set rng = Range("a4", Cells(Rows.Count, "d").End(xlUp))

    Sheets("정렬1").Sort.SortFields.Clear
    '시트 '정렬1'에 사용자정의목록으로 정렬되는게 있다면 지워라 (정렬 코딩시 필수 문장) .

    rng.Sort rng(1, 1), xlDescending, rng(1, 2), , xlAscending, Header:=xlNo, Orientation:=xlSortColumns

    'Orientation : 정렬 방향

https://www.youtube.com/watch?v=OJ3fg69znFg&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=30

엑셀 VBA #29 / VBA함수(split) [VBA]

1. 목적 : VBA함수 Split 사용법 습득
2. Split함수 구조
VBA.Split(대상,구분자)   또는
Split(대상, 구분자)

3. 사용
  3.1. For~Next문
    Dim s As Variant
    Dim i As Long

    s = Split(Range("A2"), " ")

    For i = 0 To UBound(s)
        Cells(i + 5, "A") = s(i)
    Next

  3.2. For Each~Next문
    Dim s As Variant
    Dim j As Long

    For Each s In Split(Range("A2"), " ")
        Cells(j + 5, "A") = s
        j = j + 1
    Next

@ayje5718   2년 전
좋은강의 잘 보고 있습니다. 감사합니다. 이전 강의에서 동적 배열을 선언하는 영상을 보고 해당 영상을 보니 갑자기 헷갈립니다 ㅠ 변수방에 값을 받아 배열로 출력하는부분에 동적 배열을 선언해 사용한다고 생각했는데 아니네요 ㅠ 그럼 변수에 배열을 선언하는건 어떤 경우인가요
      @user-dq4gc5hl5j   2년 전
      안녕하세요, 반갑습니다 :)
       프로시저의 처리속도를 높이기 위해서는 배열 사용이 필수입니다. 
       그런데 배열을 이해하고, 실무에 적용하는데 시간이 조금 소요됩니다.
       한번에 이해가 안되도 조급해하지 마시고
       여러 코딩 경험을 통해 배열 개념이 정리되는 시간을 주셔요^^
       먼저 VBA #126 영상을 살펴보시길 추천합니다.

https://www.youtube.com/watch?v=OCbhs5a71ZQ&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=29

엑셀 VBA #28 / 워크시트 함수 [VBA] - Match이용


1. 목적 : VBA내에서 워크시트 함수 사용법 습득

2. 구조
Application.WorksheetFunction.함수명
WorksheetFunction.함수명
Application.함수명
**Best 구조 : Application.함수명

3. 활용

위 표에서 C로 시작되는 제품코드는 원본자료 A2:A9영역 중 몇 번째 있는지를 메세지 박스로 구현하면
MsgBox Application.WorksheetFunction.Match("C*", Range("A2:A9"), 0)

4번째 행에 있다고 나온다.

그러면 D로 시작하는 제품코드는 어디있는지 보자...
MsgBox Application.WorksheetFunction.Match("D*", Range("A2:A9"), 0)

원본자료의 제품코드에는 D로 시작하는 코드가 없기 때문에 오류가 발생한다.

위와 같은 오류 발생시 프로시져를 종료시키는구문은 다음과 같다.
If IsError(Application.WorksheetFunction.Match("D*", Range("A2:A9"), 0)) Then
   Exit Sub
End If

하지만 여전히 오류 메세지가 나온다.....

이럴 때 아래 구문처럼 WorksheetFunction 지우면 오류 발생 메세지가 나오지 않는다.
그래서 2항에서 Best 구조는 Application.함수명라고 했던 것이다.
If IsError(Application.WorksheetFunction.Match("D*", Range("A2:A9"), 0)) Then
   Exit Sub
End If

4. 심화
Dim rs As Range
Dim r As Long
Set rs = Range("B2:B9")

If IsError(Application.Match(Application.Min(rs), rs, 0)) = False Then
    r = Application.Match(Application.Min(rs), rs, 0)
    Range("B10") = Cells(r + 1, "A")
End If

Application. Match(Application.Min(rs), rs, 0)) 이해.....
Min함수를 이용하여 B2:B9영역에서 최소값을 찾는다.

B2:B9영역에서 찾은 최소값에 매칭(Matching)하는 행위치를 찾는다. 

https://www.youtube.com/watch?v=RAqB6wkP5SA&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=28