힘껏 차라

‘Hyperlink’ 라는 함수를 사용하면 되고 주의할 점은 시트를 참조할 때는 시트명 앞에 ‘#’을 붙여주어야 한다는 점.

그리고 작성하면서 ‘Find’ 라는 아주 유용한 함수 하나를 배웠다.

Sub cell_link()
    sn = “List”
    lc = Range(“A1″).End(xlDown).Address
    rs = “D4″
    re = Range(sn + “!” & rs).End(xlDown).Address

    i = 1
    For Each c In Range(“A1:” & lc)
        ci = “=COUNTIF(” + sn + “!” + rs + “:” + re + “,” + c.Address + “)”
        Range(“C1″).Value = ci
        If (Range(“C1″).Value > 0) Then
            ca = Range(“” + sn + “!” + rs + “:” + re + “”).Find(What:=c.Value).Address
            Range(“B” & i).Value = “=HYPERLINK(“”#” + sn + “!” & ca & “”",”"” + c.Value + “”")”
        End If
        i = i + 1
    Next c
End Sub

엑셀 파일에 일별로 시트를 만들어서 야근자 기록을 했다. 월말이 되어 각 직원의 야근 회수를 집계해야 하는데 일별로 작성된 시트가 20개 넘는다. 쉽게 집계할 수 있는 방법이 없을까?

아래는 각 시트에서 야근자 이름이 있는 컬럼만 하나의 시트로 전부 모아오는 매크로다. 이렇게 모아만 놔도 countif 등의 함수로 간단히 집계가 가능하다. ( 아래 매크로에서는 긁어 모은 야근자명을 distinct 한 후 카운트하는 함수는 별도로 만들어서 콜해서 사용했다. )

얼마 전부터 팀 단위로 관리하기 시작한 야근일지 집계 때문에 옆에 분이 도움 청해와서 급조해 본 매크로인데 여러 시트에서 특정 컬럼값들 긁어올 때 응용 가능할 것 같아서 Window Live Writer 테스트도 해볼 겸 메모.

Sub MakeSummary()
Dim c As Variant
Dim lc As Variant
Dim k As Integer
Dim l As Integer
Sheets(“SUMMARY”).Select

Range(“A1:H1″).EntireColumn.ClearContents
For i = 1 To Sheets.Count
A$ = Sheets(i).Name

If (A$ = “summary”) Then GoTo 10
l = 6
lc = Sheets(A$).Range(“B” + Format(l + 1)).End(xlDown).Address
For Each c In Sheets(A$).Range(“B” + Format(l + 1) + “:” & lc)
l = l + 1
k = k + 1
Range(“D” & k).Value = “=’” + A$ + “‘!R” + Format(l) + “C2″
If (Range(“D” & k).Value = 0) Then
l = l – 1
k = k – 1
GoTo 10
End If

Range(“A” & k).Value = A$
Range(“B” & k).Value = “=’” + A$ + “‘!R” + Format(l) + “C2″
Next c
10 Next i

Range(“D1″).EntireColumn.ClearContents

Call distinct
Call Count_Overwork
End Sub

Sub distinct()
….
End Sub

Sub Count_Overwork()
j = 1
lc = Range(“B1″).End(xlDown).Address
lc1 = Range(“E1″).End(xlDown).Address
For Each c In Range(“E1:” & lc1)
Range(“G” & j).Value = “=COUNTIF(B1:”+lc+”, E”+Format(j) + “)”
j = j + 1
Next c
End Sub

혹 꼼꼼히 함수 읽어 본 이라면 발견했을 수도 있는데 중간에 각 시트의 컬럼에서 마지막 값 있는데 까지 긁어 오는 부분( ‘ For Each c In Sheets(A$).Range(“B” + Format(l + 1) + “:” & lc) … ‘ )  에서 ***.End(xlDown).Address 함수의 문제 때문에 약간의 꽁수를 썼다. 이 부분을 깔끔하게 처리하면 좀 더 재활용성 높아질 듯 싶은데 누구 힌트 좀 주실 분 없으려나?

p.s : Window Live Writer, 꽤 깔끔하게 작동한다. 앞으로 ScribeFire 와 함께 잘 써먹어야겠다.