VBA 엑셀 업무 자동화 정리

Excel VBA 설정법

엑셀 상단바에서 리본 메뉴 사용자 지정 클릭하고

개발 도구 체크한 뒤 확인

Excel 옵션 -> 보안 센터 -> 보안 센터 설정 -> 매크로 설정 에 가서
화면에 보이는 것 처럼 체크한 뒤 확인

이제 개발 도구 -> Visual Basic 클릭해서 들어가서 VBA로 자동화 코드를 짤 수 있다.

VBA Cheat Sheet

• CurrentRegion : 기준 영역에서 Data가 있는 인접한 영역

• UsedRange : Sheet에서 사용된 영역 전체

• SpecialCells(2) : Data가 있는 셀

• Cells(1,1)에 인접한 모든 셀 선택
  Range("A1").CurrentRegion.Select

• 활성화된 Sheet에서 값이 있는 모든 셀 선택
  ActiveSheet.UsedRange.SpecialCells(2).Select

• 마지막 열 찾기
  lastColumn = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column

• 데이터 이동 ( 역순실행 For i = lastColumn to 1 Step -1 )
  For i = 1 To lastColumn
  sourceSheet.Cells(1, i).Copy
  targetSheet.Cells(i, 1).PasteSpecial Transpose:=True
  Next i ' i가 lastColumn 보다 크면 For문 종료

• "기준"에서 1,1 shift된 셀 선택
  Cells.Find("기준").Offset(1, 1).Select

• 숫자 2에서 1,1 크기의 셀범위 선택
  Cells.Find(2).Resize(7, 1).Select

• End() -> 1,2,3,4 / xlUp, xlDown, xlToLeft, xlToRight
  -> 좌 우 상 하 부분 활성화된 열을 기준으로 영역 끝에 있는 cell 선택.
  End(3).Row : 마지막 행번호

• IIf(IsEmpty(jobDetailcategory) Or allocation = "", "0", jobDetailcategory)
  조건: IsEmpty(jobDetailcategory) Or allocation = ""
  jobDetailcategory가 비어있거나 allocation이 빈 문자열인 경우
  조건이 참이면 "0"을 반환. 거짓일 때 값: jobDetailcategory

실전 예시

Sub ProcessMobilizationPlanData()
    Dim wsMob As Worksheet
    Dim wsOutput As Worksheet
    Dim startDate As Date
    Dim endDate As Date
    Dim projectName As String
    Dim lastRow As Long
    Dim i As Integer, j As Integer
    Dim monthCount As Integer
    Dim jobCount As Integer
    Dim outputRow As Long
    
    ' Set reference to worksheets
    Set wsMob = ThisWorkbook.Worksheets("Mob. Plan")
    Set wsOutput = ThisWorkbook.Worksheets("입력포맷변환")
    
    ' Get project name, start date, and end date
    projectName = wsMob.Range("C3").Value
    startDate = wsMob.Range("C4").Value
    endDate = wsMob.Range("C5").Value
    jobCount = wsMob.Range("C6").Value
    
    ` number of M ( month )
    monthCount = DateDiff("m", startDate, endDate) + 1
    
    ' Find the first empty row in the output sheet
    outputRow = wsOutput.Cells(wsOutput.Rows.Count, "F").End(xlUp).Row + 1
    
    ' Find last row with job titles
    lastRow = wsMob.Cells(wsMob.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through job titles
    For i = 12 To lastRow ' Assuming job titles start from row 12
        Dim jobTitle As String
        Dim jobCategory As String
        Dim jobSubcategory As String
        Dim jobDetailcategory As String
        
        jobTitle = wsMob.Cells(i, "B").Value
        jobCategory = wsMob.Cells(i, "C").Value
        jobSubcategory = wsMob.Cells(i, "D").Value
        jobDetailcategory = wsMob.Cells(i, "E").Value
        
        ' Process each month for the current job
        For j = 1 To monthCount
            Dim currentStartDate As Date
            Dim currentEndDate As Date
            Dim allocation As Variant
            currentStartDate = DateSerial(Year(startDate), Month(startDate) + j - 1, 1)
            currentEndDate = DateSerial(Year(currentStartDate), Month(currentStartDate) + 1, 0)
            allocation = wsMob.Cells(i, "G").Offset(0, j).Value
            
            ' Write data to output sheet
            wsOutput.Cells(outputRow, "C").Value = projectName
            wsOutput.Cells(outputRow, "M").Value = IIf(IsEmpty(jobDetailcategory) Or allocation = "", "0", jobDetailcategory)
            wsOutput.Cells(outputRow, "F").Value = Format(currentStartDate, "yyyy-mm-dd")
            wsOutput.Cells(outputRow, "G").Value = Format(currentEndDate, "yyyy-mm-dd")
            wsOutput.Cells(outputRow, "I").Value = IIf(IsEmpty(allocation) Or allocation = "", "0", allocation)
            wsOutput.Cells(outputRow, "J").Value = jobTitle
            wsOutput.Cells(outputRow, "K").Value = jobCategory
            wsOutput.Cells(outputRow, "L").Value = jobSubcategory
            outputRow = outputRow + 1
        Next j
    Next i
    MsgBox "Data processing complete. Please check the '입력포맷변환' sheet.", vbInformation
End Sub

Dim을 이용해서 변수 선언
ThisWorkbook.Worksheets 로 worksheet 접근
wsMob.Range("C3").Value 등으로 엑셀 데이터 접근

직무 정보를 가져와서 월 수 만큼 반복문을 돌려서 값을 넣어줌
직무가 4개고 월 수가 10달이라면 4 x 10 해서 40개의 행을 만들어서 넣어주는 것.

엑셀도 거의 안 써봤고 VBA도 처음이였지만 생각보다 직관적이고 다루기 쉬웠다