VBA (Excel) - 특정 Column 의 합계 구하기

이번에는 특정 컬럼의 숫자를 더하는 코드를 만들어 보겠습니다.

제가 그냥 만들어본 표 입니다. (State는 제가 있는 Minnesota 의 약자이구요. 그 안의 값들은 그냥 임의로 넣은 겁니다.)

여기서 Aug 컬럼의 숫자들을 더한 후 New_Sheet 라는 Sheet 에 그 더한 값을 넣겠습니다.

여기서 조건을 문서를작성하다 보면 양식은 위와 같이 되지만 값의 Cell 값은 바뀔 수 있는 상황입니다.

예를 들어 어떤 사람은 이 표를 엑셀 쉬트의 A1 서부터 작성하는 사람이 있고 또 어떤 사람은 한 한 B3 정도부터 작성하는 사람이 있습니다.

그러면 각 값들의 위치들도 그에 따라 달라질 건데요.

이럴 경우에도 구애 받지 않고 Aug 컬럼 값들의 합계를 구해 보겠습니다.

그러기 위해 우선 Aug 의 위치를 알아 내겠습니다.

그리고 바로 그 밑의 Cell 값을 받고 맨 마지막의 Cell 값을 알아 냅니다.

그래서 그 Cell 들의 값을 더하겠습니다.

여기서 어떤 달들은 중간중간 혹은 맨 처음이나 맨 마지막 값이 비어 있을 수 있습니다.

그래서 맨 마지막 Cell 값은 항상 값이 채워져 있는 Seq 나 State로 가서 값이 있는 맨 마지막 Cell을 알아 내겠습니다.

우선 이런한 작업을 할 때 값을 담을 변수들을 만들겠습니다.

    Dim firstCell As Range
    Dim lastCell As Range
    Dim state1stCell As Range
    Dim sumCell As Range

    Dim lastRow As Long

firstCell과 lastCell 은 Aug 컬럼의 첫번째와 마지막번째 Cell 값입니다. 

state1stCell 은 state 컬럼의 첫번째 Cell 값이구요.

sumCell은 Aug 컬럼의 값들을 모두 더한 값을 넣을 변수 입니다.

그리고 lastRow는 Long 타입인데요. State 컬럼에서 값이 있는 맨 마지막 Row를 구해서 넣을 변수 입니다.

지난번에 올린 엑셀 파일을 보면 저 테이블은 두번째 Sheet에 있고 첫번째 Sheet 에 저 Sheet로 가는 링크가 있습니다.

우선 이 링크를 클릭하는 코드를 작성하겠습니다.

    Range("B4").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
   
    Cells.Find(What:="Aug", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

일단 첫번째 sheet 에서 이 함수를 실행시킨다고 가정하구요.

아무곳에나 커서를 놓습니다. (여기서는 B4).

그리고 첫번째 링크를 클릭합니다.

그러면 두번째 Sheet로 이동했을 겁니다. 여기서 Find 명령어로 Aug라는 필드를 찾아서 그 Cell 을 Activate 합니다.

(위 코드는 Macro를 실행해서 만든 코드 입니다.)

여기까지 하면 아래와 같이 Aug 컬럼이 선택 됩니다.

그러면 Aug 컬럼의 첫번째 Cell 을 구할 수 있습니다.

   ActiveCell.Offset(1, 0).Activate
   
    ''''''' Set FistCell
    Set firstCell = ActiveCell

현재 활성화된 Cell (여기서는 Aug) 에서 한칸 아래로 내려가서 그 Cell을 firstCell 에 담았습니다.

이제 firstCell 에는 62.28 이 있는 Cell 이 담겨져 있습니다.

    Cells.Find(What:="State", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
       
    ActiveCell.Offset(1, 0).Activate
    Set state1stCell = ActiveCell
    state1stCell.Activate

그 다음에는 Find 를 사용해서 State 가 있는 Cell을 찾아서 활성화 시킵니다.

이제 그 활성화 된 Cell 바로 아래 Cell을 state1stCell 에 담았습니다.

그리고 그 state1stCell을 다시 활성화 시킵니다.

이제 State 컬럼의 첫번째 Row 에 있는 Cell 이 활성화 돼 있을 겁니다.

   state1stCell.End(xlDown).Activate
    Set statelastCell = ActiveCell
   
    lastRow = statelastCell.Row

이제 End(xlDown)을 이용해서 State 컬럼의 맨 마지막 Row로 이동합니다.

그리고 그 row의 값을 lastRow에 담습니다.

    firstCell.Activate

    firstCell.Offset(lastRow - firstCell.Row, 0).Activate
   
    ''''''' Set LastCell
    Set lastCell = ActiveCell

이제 값이 있는 맨 마지막 Row의 값을 알았으니 다시 Aug 컬럼으로 가겠습니다.

firstCell 에서 State 컬럼에서 얻은 맨 마지막 Row 값을 이용해서 Aug의 맨 마지막 Row로 갑니다.

그리고 이 Aug의 맨 마지막 Row를 lastCell에 담습니다.

이러면 Aug의 맨 첫번째 Cell 과 맨 마지막 Cell을 얻게 됐습니다.

이것만 알면 Aug의 첫번째 Cell 에서 마지막 Cell 에 걸쳐 있는 값의 총계를 알아 낼 수가 있습니다.

   ''''''' Set sumCell
    Set sumCell = lastCell.Offset(1, 0)
   
    Range(firstCell, lastCell).Activate
   
    '''''' Get Sum of Aug
    sumCell.Value = Application.WorksheetFunction.Sum(Selection)
    sumCell.Activate
    Selection.Copy

구한 합계는 Aug의 맨 마지막 Cell의 밑에 Row에 적어 넣을겁니다.

그래서 sumCell은 lastCell의 바로 밑의 Cell로 설정해 두었습니다.

위에서도 사용했던 Offset을 여기서도 사용했습니다.

이제 Aug의 첫번째 셀에서부터 마지막 셀까지 범위 설정을 하고 그 합을 sumCell에 넣었습니다.

그리고 그 값을 Copy 했습니다.

이제 클립보드 내에는 Aug 컬럼의 값들의 합이 담겨져 있습니다.

    Sheets("New_Sheet").Select
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Aug Sum"
    Range("D6").Select

이제 New_Sheet로 이동해서 D3 위치에 복사한 값을 붙여넣기 합니다.

Selection.PasteSpecial은 Macro를 이용해서 구한 코드 입니다.

여기서는 단순하게 붙여넣기를 한게 아니라 그 Value 를 붙여넣기 했습니다.

제가 필요한 것은 그 셀의 공식이 아니라 값이니까요.

이제 복사해 넣은 값의 왼쪽 셀(C3)로 가서 AugSum 이라고 글자를 써 넣습니다.

이렇게 해서 Aug 컬럼의 모든 값을 더해서 New_Sheet 에 복사해 넣는 코드가 완성됐습니다.

이제 저 양식만 맞추면 표를 어느 위치에 작성하든 Aug 필드의 값들의 합을 구할 수가 있게 됐습니다.

전체 소스코드는 아래와 같습니다.

Sub getSumofRows()
'
' getSumofRows Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'

    Dim firstCell As Range
    Dim lastCell As Range
    Dim state1stCell As Range
    Dim sumCell As Range
    Dim statelastCell As Range

    Dim lastRow As Long
   
    Range("B4").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
   
    Cells.Find(What:="Aug", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
       
    ActiveCell.Offset(1, 0).Activate
   
    ''''''' Set FistCell
    Set firstCell = ActiveCell
   
    Cells.Find(What:="State", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
       
    ActiveCell.Offset(1, 0).Activate
    Set state1stCell = ActiveCell
    state1stCell.Activate
   
    state1stCell.End(xlDown).Activate
    Set statelastCell = ActiveCell
   
    lastRow = statelastCell.Row
   
    firstCell.Activate

    firstCell.Offset(lastRow - firstCell.Row, 0).Activate
   
    ''''''' Set LastCell
    Set lastCell = ActiveCell
   
    ''''''' Set sumCell
    Set sumCell = lastCell.Offset(1, 0)
   
    Range(firstCell, lastCell).Activate
   
    '''''' Get Sum of Aug
    sumCell.Value = Application.WorksheetFunction.Sum(Selection)
    sumCell.Activate
    Selection.Copy
 
    Sheets("New_Sheet").Select
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Aug Sum"
    Range("D6").Select
End Sub

제가 엑셀 프로그래머가 아니라서 코드가 깔끔하지는 않을 겁니다.

배우시는 분들은 이걸 좀 더 깔끔하게 다듬으시면 더 공부가 잘 될 겁니다.

ForTesting.xlsm

필요하신 분은 위 파일 다운 받으시면 안에 소스코드가 있습니다.

Cntr-Shift-A 는 지난 글에서 다뤘던 Sheet 만드는 매크로이고 Cntr-Shift-B 는 그 새로 만든 매크로에 Aug 컬럼의 값들의 합계를 구해 넣는 매크로 입니다.

둘 다 첫번째 Sheet 에서 실행 하셔야 됩니다.