VBA 4주 완성 : 엑셀업무 자동화 -2주차 2교시-

2주차 강의 내용 -2교시-

For - 반복문

예제-1 

' i 라는 변수를 1 부터 10까지 반복
For i = 1 To 10

   MsgBox i

Next i '<- i 생략가능

예제-2 

' i라는 변수를 1 부터 10까지 반복하면서 특정 워크시트에 A1 = 1, A2 = 2 .... A10 = 10 으로 작업
For i = 1 To 10

  WS.Range("A" & i).Value = i

Next i '<- 생략가능

예제-3

'WB 통합문서 안에 있는 Worksheets의 이름을 보려고 함
For Each WS In WB.WorkSheets

   MsgBox WS.Name

Next

예제-4

'특정 워크시트 안에 범위를 A1:A10 셀까지 하나씩 돌아가면서 확인
For Each Rng In WS.Range("A1:A10")

  Rng.Value = Rng.Address

Next

 

IF-조건문

예제-1 

'A1 셀의 값이 A2 셀보다 클경우 "양수" 아니면 "음수"
If WS.Range("A1").Value > 0 Then
   WS.Range("A2).Value = "양수"

Else
   WS.Range("A2").Value = "음수"
End if

예제-2

'A1 셀의 값이 A2 셀보다 클경우 "양수", 0일 경우 "-" , 나머지 "음수"
If WS.Range("A1").Value > 0 Then
   WS.Range("A2).Value = "양수"

Else if WS.Range("A1").Value = 0 Then
   WS.Range("A2").Value = "-"

Else
   WS.Range("A2").Value = "음수"
End if

예제-3 ▶ For 문이랑 응용

'통합문서 안에서 Worksheets 를 하나씩 돌아가면서 보는데 Worksheet의 이름이 5글자 보다 클경우 MsgBox를 출력하고 반복문 종료
For Each WS In WB.Worksheets
   If Len(WS.Name) > 5 Then
      MsgBox WS.Name
      Exit For
   End If
Next

예제-4

'A1:A10 범위안에 돌아가면서 셀의 값이 80보다 클경우 "Pass" 아니면 "Fail" 입력
For Each Rng In WS.Range("A1:A10")
   If Rng.Value > 80 Then
      Rng.Value = "Pass"
   Else
      Rng.Value = "Fail"
   End If
Next

반응형

실습 - 1

Sub CreateToC()
 
'변수선언
Dim WB As Workbook
Dim WS As Worksheet
Dim i As Long
 
'Workbook, Worksheet 은 개체이므로 Set 함수 사용
Set WB = ThisWorkbook
 
'Set 시트 = 통합문서.WorkSheet("시트이름" or 순번)
Set WS = WB.Worksheets("목차")
 
----------------------------------------------------------------------------------------------------------------------------------------------
 

'반복문 작성
For i = 1 To WB.Worksheets.Count

'Dubug.print -> 직접 실행창에 값을 보여주기 위한 함수
Debug.Print i 

Next

 ----------------------------------------------------------------------------------------------------------------------------------------------

For i = 1 To WB.Worksheets.Count

'WB 통합문서에서 번호 순서별 Sheet의 이름을 확인 하겠다.
Debug.Print WB.Worksheets(i).Name 

Next

 ----------------------------------------------------------------------------------------------------------------------------------------------

For i = 1 To WB.Worksheets.Count

'1~WB.Worksheets.Count까지 반복하므로 C1 ~ C(WB.Worksheets.Count) 값까지 시트별 이름 목차 입력
WS.Range("C" & i).Value = WB.Worksheets(i).Name

Next

 ----------------------------------------------------------------------------------------------------------------------------------------------

End Sub
 

실습 - 2

For i = 1 To WB.Worksheets.Count

'1~WB.Worksheets.Count까지 반복하므로 C1 ~ C(WB.Worksheets.Count) 값까지 시트별 이름 목차 입력
WS.Range("C" & i).Value = WB.Worksheets(i).Name

Next

'C1:C10 까지 Sheet의 이름이 나열되어 있는데 해당 Sheet 로 바로 이동할 수 있도록 "링크"를 걸어보자!

For i = 1 To WB.Worksheets.Count

 

'1~WB.Worksheets.Count까지 반복하므로 C1 ~ C(WB.Worksheets.Count) 값까지 시트별 이름 목차 입력

      WS.Range("C" & i).Value = WB.Worksheets(i).Name

 

'Anchor = 링크를 추가할 셀

'Address = 웹페이지 주소 or 파일경로

'SubAddress = [선택인수] 내부 시트로 이동할 때 주소 => 시트이름!위치할 셀

 

 

' Ci셀 ▶ 링크, 대상 목표셀 : 시트명!A1

      WS.Hyperlinks.Add WS.Range("C" & i), "", WB.Worksheets(i).Name & "!A1"

Next

실습 - 3  찾기 및 바꾸기 매크로

Sub FindReplace()
 
'변수선언
Dim WS As Worksheet
Dim FindValue As String
Dim ReplaceValue As String
Dim Rng As Range
Dim R As Range
 
'Set 할당
Set WS = ThisWorkbook.Worksheets("확진자경로")
 
' Set 선언 안하는 이유는 FindValue, ReplaceValue 는 문자(String)로 할당 되어 있기 때문에 값으로 인식함
FindValue = WS.Range("J4").Value
ReplaceValue = WS.Range("J5").Value
 
 
'선택한 범위를 받아오게 하기
Set Rng = Selection
 
 
'현재 선택한 범위를 하나씩 돌아가면서 찾을 값과 동일하면 바꿀 값으로 바꿔주기
 
 
For Each R In Rng
   If R.Value = FindValue Then
     R.Value = ReplaceValue
     R.Interior.Color = 65535 '바뀐 값에 노란색 입히기
   End If
Next
   
End Sub

 

미션 - 1 ▶ 시트 목차 생성 매크로 만들기

Sub SheetListMacro()

 

'변수생성

Dim WB As Workbook

Dim WS As Worksheet

Dim Rng As Range

 

'Set 할당

Set WB = ThisWorkbook

 

Set WS = WB.Worksheets("확진자경로")

 

 

'반복문 작성하기

For i = 1 To WB.Worksheets.Count

 

'1~WB.Worksheets.Count까지 반복하므로 M1 ~ M(WB.Worksheets.Count) 값까지 시트별 이름 목차 입력

WS.Range("M" & i).Value = WB.Worksheets(i).Name

Next

 

End Sub


==============================================================================

추가! 하이퍼링크 삽입하기

Sub SheetListMacro()

 

'변수생성

Dim WB As Workbook

Dim WS As Worksheet

Dim Rng As Range

 

'Set 할당

Set WB = ThisWorkbook

 

Set WS = WB.Worksheets("확진자경로")

 

 

'반복문 작성하기

For i = 1 To WB.Worksheets.Count

 

'1~WB.Worksheets.Count까지 반복하므로 M1 ~ M(WB.Worksheets.Count) 값까지 시트별 이름 목차 입력

WS.Range("M" & i).Value = WB.Worksheets(i).Name

'Hyperlinks = 하이퍼링크 삽입
'Anchor = 링크를 추가할 셀
'Address = 웹페이지 주소 or 파일경로
'SubAddress = [선택인수] 내부 시트로 이동할 때 주소 => 시트이름!위치할 셀

WS.Hyperlinks.Add WS.Range("M" & i), "", WB.Worksheets(i).Name & "!M1"

Next

 

End Sub

 

미션 - 2 ▶ 찾기 및 바꾸기 매크로 만들기

Sub Find_Replace()
 
'변수선언
 
Dim WS As Worksheet
Dim FindValue As String
Dim ReplaceValue As String
Dim Rng As Range
Dim R As Range
 
'Set 할당
Set WS = ThisWorkbook.Worksheets("확진자경로")
 
' Set 선언 안하는 이유는 FindValue, ReplaceValue 는 문자(String)로 할당 되어 있기 때문에 값으로 인식함
 
FindValue = WS.Range("J4").Value
 
ReplaceValue = WS.Range("J5").Value
 
 
'선택한 범위 받아오기
Set Rng = Selection
 
'For Each 함수를 이용하여 현재 선택한 범위를 하나씩 돌아가면서 찾을 값과 동일하면 바꿀 값으로 바꿔주기
 
For Each R In Rng
 
   If R.Value = FindValue Then
      R.Value = ReplaceValue
   End If

Next
 
 
End Sub
 
 

 

보너스 미션  ▶ XLOOKUP 함수 만들기

Function MyXLookUp(lookup_value, lookup_range As Range, return_range As Range)

' 변수 선언
' i = 찾을범위 반복할 순번
Dim i As Long

' 찾을범위의 시작부터 끝까지 돌아가면서
For i = 1 To lookup_range.Rows.Count

'찾을범위의 i번째 값이 찾을값과 동일하면
'※힌트 : If ○○○.Cells(○).value = ○○ Then ... End If


   If lookup_range.Cells(i).Value = lookup_value Then
              MyXLookUp = return_range.Cells(i).Value

   Exit Function

End If

'MyXLookup 함수의 결과값으로 반환범위의 i번째 값을 출력한 후 명령문을 강제로 종료한다.
'※힌트 : MyXLookup = ○○○.Cells(○).Value
'※힌트 : Exit Function

Next

End Function