반응형
블로그 이미지
개발자로서 현장에서 일하면서 새로 접하는 기술들이나 알게된 정보 등을 정리하기 위한 블로그입니다. 운 좋게 미국에서 큰 회사들의 프로젝트에서 컬설턴트로 일하고 있어서 새로운 기술들을 접할 기회가 많이 있습니다. 미국의 IT 프로젝트에서 사용되는 툴들에 대해 많은 분들과 정보를 공유하고 싶습니다.
솔웅

최근에 받은 트랙백

글 보관함


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




제가 그냥 만들어본 표 입니다. (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 에서 실행 하셔야 됩니다.


반응형

Comment


이번에는 새로운 Sheet 를 생성하는 코드를 작성해 보겠습니다.


일단 Excel file 내의 sheet 들의 맨 마지막 위치에 새로운 sheet 를 생성해서 New_Sheet 라고 이름을 붙이겠습니다.


다만 이 New_Sheet 라는 sheet 가 있으면 새로운 sheet를 생성하지 않고 이 New_Sheet 가 있다는 Message Box를 보이겠습니다.


코드는 아래와 같습니다.


Sub CreateNewSheet()
'
' CreateNewSheet Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Dim sh As Worksheet, flg As Boolean
    For Each sh In Worksheets
        If sh.Name Like "New_Sheet" Then flg = True: Exit For
   
    Next
   
    If flg = True Then
        MsgBox "New_Sheet Exist!"
    Else
        ActiveWindow.ScrollWorkbookTabs Position:=xlLast
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = "New_Sheet"
    End If
End Sub


이 함수의 이름은 CreateNewSheet() 로 했습니다.

그다음두 줄은 주석입니다.

이 매크로를 생성할 때 단축키는 Ctrl+Shift+A 로 만들었습니다.


이것은 지난 글에서 다룬 Macro 만들기 할 때 단축키를 넣은 것입니다.

아직 코드 내에서 단축키를 만드는 것은 모릅니다.


첫째 줄에 나오는 DIM 은 변수에 공간을 할당하는 statement 입니다.

Worksheet 형태인 sh 라는 변수를 생성했습니다.

그리고 Boolean type 의 flg라는 변수도 만들었구요.


그리고 for loop문이 나옵니다.

Worksheets 내에 있는 Worksheets들을 차례로 불러내서 어떤 일을 할 겁니다.

이때 불러 낼 때는 해당 Worksheet를 sh라는 변수에 담을 거구요.

이것을 하려고 첫째줄에 sh라는 변수 공간을 만들어 둔 겁니다.


For 문 안을 보면 sh에 담겨있는 Worksheets의 이름을 가져와서 "New_Sheet" 라는 문자와 비교를 하네요.

이 문자가 포함돼 있는 sheet 이름이 있으면 flg 라는 Boolean type 변수에 True를 할당합니다.

그리고 For 문을 Exit 하구요.

만약에 없으면 Next 가 실행되서 그 다음 Worksheet를 살펴봅니다.


이 엑셀 파일에 있는 Worksheet 들을 다 돌았으면 For 문에서 빠져 나오게 되겠죠.



For Loop 문의 신택스는 아래와 같습니다.

For counter [ As datatype ] = start To end [ Step step ]
    [ statements ]
    [ Continue For ]
    [ statements ]
    [ Exit For ]
    [ statements ]
Next [ counter ]


자세한 것은 링크를 따라가서 보시구요.


보니까 For Each Loop는 좀 다르군요.


신택스는 아래와 같습니다.

For Each element [ As datatype ] In group
    [ statements ]
    [ Continue For ]
    [ statements ]
    [ Exit For ]
    [ statements ]
Next [ element ]


이것도 자세한 것은 링크를 따라가서 보세요.



이제 For Each Loop 에서 flg 에 대한 값이 할당이 됐습니다.

그 다음 if 문에서는 이 flg 가 True 일 경우에는 "New_Sheet Exist!" 라는 글자가 있는 MsgBox를 띄우고 flg가 True가 아닐 경우에는 그 다음일을 합니다.


우선 현재엑셀 파일에서 Worksheet이 맨 마지막으로 가구요.

그 다음에 맨 마지막 Sheet 다음에 새로운 Sheet를 만듭니다.

그리고 그 맨 마지막에 있을 Sheet의 이름을 "New_Sheet"라고 지어 줍니다.


If 문의 신택스는 이렇습니다.

' Multiple-line syntax:
If condition [ Then ]
    [ statements ]
[ ElseIf elseifcondition [ Then ]
    [ elseifstatements ] ]
[ Else
    [ elsestatements ] ]
End If

' Single-line syntax:
If condition Then [ statements ] [ Else [ elsestatements ] ]


이렇게 하면 처음에 원했던 대로 엑셀 파일의 맨 마지막 Sheet 에 Work_Sheet 라는 새로운 Sheet를 만듭니다.

기존에 이 Sheet가 있으면 안 만들구요.


ForTesting.xlsm


제가 작업한 파일을 올려 놓으니까 필요하신 분들은 참조하세요.


한가지 Tip 으로 말씀 드릴것은 Macro를 쓰려면 엑셀파일을 Macro가 가능한 버전으로 Save As를 해야 합니다.

그러면 저렇게 확장자가 xlsm 이 됩니다.

그렇게 하지 않으면 기껏 작업한것을 모두 날릴 수 있습니다.


반응형

Comment


직장 생활을 하다 보면 엑셀을 잘 사용하면 일을 훨씬 편하게 할 수 있겠다라는 생각을 많이 합니다.


그래서 한번 엑셀 프로그래밍을 배우려고 합니다.


그동안 마음만 먹고 있었는데 모처럼 연휴에 여유가 있어서 마음만 먹었던 것을 시작 하려고 합니다.


우선 첫번째로 Macro 를 사용하는 방법을 보겠습니다.




일단 1에서 9까지 적어 넣고 11번째 줄에서는 그 수를 모두 더했습니다. (=SUM(B2:B10))

그 오른쪽은 랜덤 함수를 이용해서 수를 랜덤하게 불러오도록 했습니다. (=RAND()*B2)


이제 이 숫자와 수식을 오른쪽에 복사해 넣을 건데요. 이 과정을 매크로로 저장하겠습니다.




첫번째로 View - Macro - Record Macro 를 선택합니다.


그러면 위와 같은 화면이 뜨는데요. 매크로 이름을 넣고 단축키를 정한 다음에 OK 버튼을 클릭합니다.

그러면 그 이후부터 진행되는 과정은 전부다 저장이 될 겁니다.

OK 버튼을 누르고 아까 작성했던 숫자와 수식들을 복사해서 그 옆에 붙여 넣겠습니다.


위와 같이 복사해 넣고 다시 View-Macro로 가셔서 이번에는 Stop Recording을 선택합니다.

그리고 나서 오른쪽에 있는 부분을 지워보세요.

아까 저는 제가 만든 매크로(Macrotest01)의 단축키를 Ctrl-Shift-a 로 했으니까 이 단축키를 누르겠습니다.


여러분은 여러분이 정한 단축키를 눌러 보세요.

그러면 아까 범위를 정하고 복사한 다음에 복사해 넣을 위치에 와서 복사한 내용을 붙여넣기 한 과정이 자동으로 빠르게 반복되면서 값이 복사될 겁니다.




View-Macro로 가셔서 View Macro를 선택하시면 위와 같은 화면을 보실 수 있습니다.

방금 전에 만들었던 Marcotest01이 있죠?

여기서 Run 버튼을 누르셔도 해당 매크로가 실행 됩니다.

지금 프로그래밍을 하나도 하지는 않았지만 사실은 Macro를 Recoding 하는 과정에서 Excel 이 프로그래밍을 자동으로 한 겁니다.


여기서는 Visual Basic이 사용되는데요.

이 과정이 어떻게 프로그래밍이 됐는지 보시죠.


소스를 보시려면 View-Macro로 View Macro를 선택하신 후 Step Info 단추를 누르셔도 되고 그냥 엑셀쉬트에서 Alt-F11 을 누르셔도 됩니다.




Alt-F11을 누르시면 위와 같이 Visual Basic Editor가 나옵니다.

Module1을 더블 클릭하면 소스가 보이는데요.

Visual Basic은 잘 모르지만 소스를 한번 보죠.


Sub라고 돼 있는데 이건 잘 모르겠구요. 자바에서 말하는 메소드나 C에서 말하는 함수 정도 되는것 같습니다.

메소드 이름이 Macrotest01() 이지요. 아까 매크로 만들때 정했던 이름입니다.

그리고 주석을 보면 Ctrl+Shift+A 가 단축키라고 나와 있구요.

그 밑에서부터 메소드의 내용인데요.

우선 B2부터 C11 까지 선택을 합니다.

그리고 선택한 부분을 Copy 하구요.

그리고 E2 부분으로 가서 Paste를 합니다.

그 다음에 나오는 Application.CutCopyMode=False는 Copy 할 부분을 설정하면 엑셀에서는 그 부분이 점선으로 표시되는데 Esc를 누르지 않는한 그 부분이 계속 클립보드 최 우선순위에 있게 되죠.

여기서는 그 선택한 것을 해제 하는 겁니다.

그 다음 줄은 End Sub으로 메소드의 끝을 알립니다.

자바에선 {} 로 시작과 끝을 처리했는데 비주얼 베이직에서는 Sub과 End Sub으로 메소드 시작과 끝을 처리하나 봅니다.


오늘은 첫시간으로 엑셀의 매크로 기능 활용하는 법과 해당 매크로의 비주얼 베이직 소스 보기를 살펴 봤습니다.


반응형

Comment

  1. simuyoung 2013.12.25 21:07

    저도 코드를 잘 모를 경우 주로 이런식으로 매크로로 코드를 확인 한 후에, 작업을 하곤 합니다.

    • 솔웅 2014.01.01 16:01 신고

      전 이제 시작이예요.
      새해 복 많이 받으시고 원하시는 일 다 성취하는 한해 되세요.

  2. 014년 새해 아침을 맞이하고 있습니다.올 한해에도 사랑과 행복이 넘치는 시간이 되시길 기원 합니다.늘 분에 넘치는 관심에 다시 한번 감사의 말씀을 올립니다.

    • 솔웅 2014.01.01 16:02 신고

      댓글 감사합니다.
      모르세님도 새해 건강하시고 모든 일 순조롭게 잘 진행되는 한해 되세요.
      일년 내내 평온한 한해 보내세요..

이전 1 2 3 다음