|
* 답변하시는 분들께 도움이 되도록 자신의 환경을 아래 항목 옆에 기재해 주세요.
- 엑셀 버전(2016,2010):
* 아래줄에 질문을 작성하세요 >>
금전출납부인데 첨부파일보고 해결좀 해주세요! 잔액을 수식이아닌 메크로로 하고싶은데 잘 안되네요!
==============[박훈이님 글에 대한 답변입니다]==============
첨부화일 참고하세요......
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Right(Sh.Name, 1) = "월" Then
If Target.Row < 11 Or Target.Row > 40 Then Exit Sub
If Target.Column = 7 Or Target.Column = 10 Then
Application.EnableEvents = False
UpDate Sh
If Application.MoveAfterReturnDirection = xlToRight Then
Target.Offset(, 1).Select
Else
Target.Offset(1).Select
End If
Application.EnableEvents = True
End If
End If
End Sub
Sub UpDate(Sh As Worksheet)
Dim iMonth As Integer, iRow As Integer
Dim rBalance As Range
Set rBalance = Sh.Range("K11:K40")
iMonth = Val(Replace(Sh.Name, "월", ""))
If iMonth = 1 Then
rBalance.Cells(1, 1).FormulaR1C1 = "=RC[-4]-RC[-1]"
Else
rBalance.Cells(1, 1).FormulaR1C1 = "='" & iMonth - 1 & "월'!R[30]C-RC[-4]-RC[-1]"
End If
For iRow = 2 To rBalance.Rows.Count
rBalance.Cells(iRow, 1).FormulaR1C1 = "=R[-1]C+RC[-4]-RC[-1]"
Next
Sh.Calculate
rBalance = rBalance.Value
End Sub
|
|