실무반

카테고리 없음·2016. 3. 9. 23:58

Sub LabelPrint()
   Dim rngCell As Range
   Dim intCol As Integer
   Dim intRow As Integer
   Dim intCount As Integer

   Worksheets("레이블").Activate
   Application.ScreenUpdating = False

   intRow = 3
   For Each rngCell In Range("이름")
      If rngCell.Row Mod 2 = 0 Then
         intCol = 2
      Else
         intCol = 5
      End If

      With ActiveSheet.Cells(intRow, intCol)
         .Offset(0, 0) = rngCell.Offset(0, 3)
         .Offset(2, 0) = rngCell.Offset(0, 4)
         .Offset(4, 0) = rngCell.Offset(0, 1)
         .Offset(6, 0) = rngCell.Offset(0, 2) & "   " & rngCell & " 귀하"
      End With

      If intCol = 5 Then
         intRow = intRow + 9
         ActiveSheet.Range("2:10").Copy
         ActiveSheet.Cells(intRow - 1, 1).Select
         Selection.PasteSpecial Paste:=xlPasteFormats
         intCount = intCount + 1
         If intCount Mod 6 = 0 Then ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell
      End If
   Next rngCell

   Application.CutCopyMode = False
   Range("A2").Activate

End Sub

Sub LabelClear()
   Rows("11:65536").Delete Shift:=xlUp
   Rows("2:10").ClearContents
   Range("A2").Activate
End Sub

Sub With문()
   Worksheets("주소록").Range("A2").Select
   With ActiveCell.Font
      .Name = "바탕체"
      .Size = 11
      .Bold = True
      .Underline = True
      .ColorIndex = 5
   End With
End Sub

Sub If문()
   Dim strPW As String
   strPW = InputBox("암호를 입력하세요")
   If strPW = "123" Then
      MsgBox "회원 인증이 되었습니다."
   Else
      MsgBox "암호가 틀립니다."
   End If
End Sub

Sub ForEach문()
   Dim rngCell As Range
   Dim intCount As Integer

   For Each rngCell In Worksheets("주소록").Range("C2:C92")
      If rngCell = "대표 이사" Then intCount = intCount + 1
   Next

   MsgBox "대표 이사는 모두 " & intCount & "명 입니다."
End Sub