[VBA] Challenges_03_Doit

Sheet1(품목검색)

 

TEXTJOIN함수 만들기

함수 설명: 문자를 병합할 범위를 Rng로 입력받아, 구분자를 지정하면 Rng의 각 셀을 돌아가며 구분자로 병합한다.

 

@ 인수 설명
Rng : 값을 병합할 범위입니다.
Delimiter : [선택인수] 구분자입니다. 기본값은 쉼표(,)입니다.

 

선택 인수 optional

단, optional는 인수의 마지막 부분에만 위치할 수 있습니다. 

Function MyTextJoin(Rng As Range, _
                    Optional Delimiter As String = ",")

Dim r As Range              'For Each문 변수
Dim Result As String      '결과로 출력할 문자열

End Function

 

Hint1) For Each r In Rng
Hint2) If r.Value <> ""Then
Hint3) Result = Result & r.Value & Delimiter

For Each r In Rng
    If r.Value <> "" Then
        Result = Result & r.Value & Delimiter
    End If
Next

 

'힌트4) MyTextJoin = Left(○○○, Len(○○○) - 1)

MyTextJoin = Left(Result, Len(Result) - 1)

 

최종

Function MyTextJoin(Rng As Range, _
                                Optional Delimiter As String = ",")

Dim r As Range              'For Each문 변수
Dim Result As String      '결과로 출력할 문자열

For Each r In Rng
    If r.Value <> "" Then
        Result = Result & r.Value & Delimiter
    End If
Next

MyTextJoin = Left(Result, Len(Result) - 1)

End Function

 

동적 범위를 받아오는 VBA

마지막 셀 이동: ctrl + 방향키

- 위에서 아래로: 중간 빈 데이터에 걸림

- 아래에서 위로: 보다 정확 -> 선택

 

구성하기

Sub DynamicRange()

Dim WS As Worksheet
Dim Column As String
Dim i As Long
Dim Address As String
Dim InitRow As Long
I
Set WS = Sheet1
'Set WS= ThisWorkbook.Worksheets("품목검색")
Column = "C"
i= WS.Range(Column & "1048576").End(xlUp).Row
InitRow = 2

Address = Column & InitRow & ":" & Column & i

MsgBox Address

End Sub

 

함수화

Function DynamicRange(WS As Worksheet, Column As String, InitRow As Long) As Range

Dim i As Long
Dim Address As String

i = WS.Range(Column & "1048576").End(xlUp).Row

If i < InitRow Then i = InitRow
	Address = Column & InitRow & ":" & Column & i

Set DynamicRange = WS.Range(Address)

End Function

 

마지막 반환 시 개체 변수(범위)이므로 Set으로 할당하는 것을 주의

 

자동 필터 VBA

- Range.Offset(행이동, 열이동): Range에서 행, 열 개수만큼 이동한 셀을 반환

 

구성화

Sub Filterltems()

'GroupRng(구분)의 조건을 비교해서, 구분에 해당하는 제품과 가격을 표시

Dim GroupRng As Range ' 필터링 할 구분 범위 (동적으로 설정!)
Dim r As Range ' GroupRng를 For Each로 하나씩 참조할 셀
Dim FilterVal As String ' 비교할 조건
Dim i As Long ' r의 값이 조건과 같을 경우, 1씩 증가할 정수

Set GroupRng = DynamicRange(Sheet1, "A", 2)
FilterVal = SheetT.Range("E2").Value

i=2
For Each r In GroupRng
If r.Value = FilterVal Then
Sheet1.Range("G" & i).Value=r.Offset(0,1).Value
Sheet1.Range("H" & i).Value = r.Offset(0, 2).Value
i=i+1
End If
Next

End Sub

 

시트 이벤트 매크로

- 이벤트 매크로: SelectionChage(셀을 클릭할 때 반응) VS Change (셀이 변경되었을 때 반응)

- Sheet1(품목검색) > (선언)을 Worksheet

 

이벤트 명령어 주의사항

값이 바뀌면 무한 루프에 빠짐

-> 마스터 코드 

Application.ScreenUpdating = False '속도
Application.EnableEvents = False '이벤트 끄기(필수)

If Not Intersect(Target, Range("셀주소")) Is Nothing Then
'실행할 명령문
End If

Application.ScreenUpdating = True
Application.EnableEvents = True

 

실시간 데이터 필터링 (시트 이벤트)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False '속도
Application.EnableEvents = False '이벤트 끄기(필수)

If Not Intersect(Target, Range("E2")) Is Nothing Then
    Filterltems
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

오류 수정 (필터링 범위 초기화)

Sub ClearRange()

Dim i As Long

i = Sheet1.Range("G1048576").End(xlUp).Row

If i > 1 Then
    Sheet1.Range("G2:H" & i).ClearContents
End If

End Sub

 

시트 이벤트 최종

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False '속도
Application.EnableEvents = False '이벤트 끄기(필수)

If Not Intersect(Target, Range("E2")) Is Nothing Then
	ClearRange
    Filterltems
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub