사용중인 매크로들 170809

반복적인 작업은 매크로를 세팅해놓으면 도움이 된다. 전체자동화보다는 각각 사용할수 있게 모듈로 나누어놓는게 나은듯

절대참조, 상대참조를 이해하고 간단하게 기록한 다음 불필요한 구문을 삭제,수정하면 된다.

(code를 포스팅 할경우 ``` 는   의 형식을 갖게 되고, <pre> </pre>는 제일 하단 서식의 형식을 갖게된다. 

입찰대비표 data

1. 양식변환

<br />Sub 입찰대비표()
'
' 입찰대비표 매크로
'

'
    Columns("H:I").Select
    Range("I1").Activate
    Selection.EntireColumn.Hidden = True
    Rows("4:5").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A6").Select
    ActiveWindow.DisplayZeros = False
End Sub

2. 비율등 입력

<br />Sub 합계1위3칸아래에서실행()
'
' 합계1위3칸아래에서실행 매크로
'

'
    ActiveCell.FormulaR1C1 = "=R[-3]C/R[-3]C[-10]"
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C[-10]-R[-5]C"
    ActiveCell.Offset(-2, 8).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C/R[-3]C[-18]"
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C[-18]-R[-5]C"
    ActiveCell.Offset(-2, 8).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C/R[-3]C[-26]"
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C[-26]-R[-5]C"
    ActiveCell.Offset(-2, 8).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C/R[-3]C[-34]"
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C[-34]-R[-5]C"
    ActiveCell.Offset(-2, 8).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C/R[-3]C[-42]"
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C[-42]-R[-5]C"
    ActiveCell.Offset(-2, 0).Rows("1:1").EntireRow.Select
    Selection.Style = "Percent"
    Selection.NumberFormatLocal = "0.0%"
    ActiveCell.Select
    Range("F6").Select
    ActiveWindow.FreezePanes = True
End Sub

분석 및 서식 추가 필요

자주쓰는 것들

날짜의 경우는 data 함수를 사용해서 yy, mm, dd 를 지정해주는 방법이 더 나았다.

<br />Sub zero()
'
' zero 매크로
'

'
    ActiveWindow.DisplayZeros = False
End Sub

Sub 사업자번호양식10자리()
'
' 사업자번호양식10자리 매크로
'

'
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],3)&-MID(RC[-1],4,2)&-RIGHT(RC[-1],5)"

End Sub


Sub 날짜8자리변환()
'
' 날짜8자리변환 매크로
'

'
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)&-MID(RC[-1],5,2)&-RIGHT(RC[-1],2)"

End Sub

출력

출력시 하단에 파일명, 탭명, 페이지 입력

<br />'
' PRINT_A3 매크로
'

'
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftFooter = "&F"
        .CenterFooter = "&A"
        .RightFooter = "&P / &N"
        .Orientation = xlLandscape
        .PaperSize = xlPaperA3
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
    End With
    Application.PrintCommunication = True
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$4:$5"
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&F"
        .CenterFooter = "&A"
        .RightFooter = "&P / &N"
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA3
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    Application.PrintCommunication = True
    Range("A2").Select
End Sub

출력 두번째

Sub setting_printer()
'
' setting_printer 매크로
'

'
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$4:$5"
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&F"
        .CenterFooter = "&A"
        .RightFooter = "&P / &N"
        .LeftMargin = Application.InchesToPoints(0.708661417322835)
        .RightMargin = Application.InchesToPoints(0.708661417322835)
        .TopMargin = Application.InchesToPoints(0.748031496062992)
        .BottomMargin = Application.InchesToPoints(0.748031496062992)
        .HeaderMargin = Application.InchesToPoints(0.31496062992126)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA3
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
End Sub

같은 값 위아래 셀 합치기

매크로 (출처: seekseek 블로그)


Sub mergecell()
'
' AutoMerge Macro
'
' 바로 가기 키: Ctrl+k
'
On Error Resume Next
'ActiveCell.Select
rowcnt = ActiveCell.Row
colcnt = ActiveCell.Column
a = Cells(rowcnt, colcnt).Value
rowcnt2 = rowcnt + 1

'빈칸이면 끝
While Cells(rowcnt2, colcnt).Value  ""
    While a = Cells(rowcnt2, colcnt).Value '같은 값이면 계속증가
       Cells(rowcnt2, colcnt).Value = "" '같으니까 값을 지워주자 ㅎㅎㅎ
       '그래야 값이 둘인데 하나는 없어진다는 메시지 창이 뜨지 않는다.
       rowcnt2 = rowcnt2 + 1
    Wend
       '이제 같은 값구역을 병합
       Range(Cells(rowcnt, colcnt), Cells(rowcnt2 - 1, colcnt)).Select

       With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge (False)
        
        '초기화

        rowcnt = rowcnt2
        a = Cells(rowcnt, colcnt).Value
        rowcnt2 = rowcnt + 1
Wend
'
End Sub

빈셀을 채워주는 매크로 및 Tip

자료를 접수하면 빈셀들이 있다. 데이타 베이스로 활용하려면 빈셀을 채워줄때가 있는데 반복적인 일인 경우가 많다. 매크로로 작성해봤지만 작동하지 않았고, 고수의 도움을 받아 매크로 및 Tip에 대해 조언을 받아서 해결했다. 그 내용에 대한 기록

1안) 매크로에서 if 문 사용

역시 고수의 도움이 있어야.

 Sub 윗값복사()

    Dim c As Range

    For Each c In Selection

        If c = "" Then

            c.Value = c.Offset(-1, 0).Value

        End If

    Next c


 End Sub

2안) F5키를 사용하는 Tip

  • 입력이 필요한 구간을 선택
  • F5 – 옵션 – 빈셀선택
  • 윗값입력(=셀)
  • Ctrl + Enter : 선택된 셀에 같은 내용이 입력된다.
  • 다시 구간 선택후 값붙여넣기

설명은 5단계지만 매우 간단하다.

Continue reading 빈셀을 채워주는 매크로 및 Tip

엑셀 파일관리 기본

엑셀 파일을 관리하는데 필요한 기본적인 사항들.

  1. 파일 용량 – 불필요한 서식제거 ★
    Healing Excel
    불필요 이름이 많으면 20kb 문서가 3.6MB로 부풀려지기도 한다.

    처리방법: Healing Excel 추가기능을 다운로드 받아 폴더 (Program Files > Microsoft Office > Office12 (또는 Office14) > XLstart) 에 놓으면 된다. 엑셀파일을 열때 불필요한 서식이 많은 경우 치료여부를 자동으로 물어본다.

  2. 파일명칭관리
    (구분) 파일명 날짜6자리 를 기본으로 한다. “(현장명) 월간보고서 130916 r12.xls”  버젼은 1.0 인경우 1.0 대신 10  을 사용한다. 점이 나중에 다운로드 받을때 파일명칭을 다르게 변환하기도 하기 때문.
  3. 클라우드 sync 서비스 비교
    – Box.net 이 Office 문서를 깨짐 없이 가장 잘 보여준다. Box.net 을 기본 excel 관리폴더로 놓는다.
    – Google Drive 은 현황 및 협업문서 중심으로 관리한다.
    – Dropbox 까지 쓴다면 Dropbox는 현안중심으로 관리한다.(완료하고 비우는 개념)
  4. DB와 excel
    – 엑셀은 DB를 편하게 가공할 수 있는 수단이다.
    – sheet 및 Tab 을 활용해서 여러문서를 붙일수 있는 장점이 있다. (클라우드 방식에서는 조금 변화해야 하지만 사람들이 이 방식을 워낙 편해하니 할수없다)
  5. Macro를 사용할수 있는가.
    – 반복적인 간단한 업무는 한번 반복을 통해 매크로를 기록하고 활용하는 것이 편하다.
    – DB의 개념이 부족하면 엑셀은 50%정도 활용하는 것이고, 매크로를 사용하지 않는다면 엑셀은 75% 수준의 활용밖에 되지 않는다.(뭐… 안써도 된다. 몸이 조금 더 고생하면 된다)

+관련글

  1. m/d aaa | #eWord. : 날짜(요일) 서식을 가장 쉽게 만드는 방법 및 기자를 위한 엑셀 기본 팁(via 박대용)링크
  2. Conditional Format | #eWord. : 조건부 검색 – 바차트, 체크리스트등에 활용.