반응형
블로그 이미지
개발자로서 현장에서 일하면서 새로 접하는 기술들이나 알게된 정보 등을 정리하기 위한 블로그입니다. 운 좋게 미국에서 큰 회사들의 프로젝트에서 컬설턴트로 일하고 있어서 새로운 기술들을 접할 기회가 많이 있습니다. 미국의 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 에서 실행 하셔야 됩니다.


반응형


반응형

이번에는 새로운 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 이 됩니다.

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


반응형


반응형

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


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


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


우선 첫번째로 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으로 메소드 시작과 끝을 처리하나 봅니다.


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


반응형
이전 1 2 3 다음