| |
▼八家九僧陀 さん：
とりあえず、なんらかの計算結果が転記されます。
もしかしたら計算式間違っているかもしれませんが。
とにかく、試してみてください。
Option Explicit
Sub Sample2()
'========================================
'年調DATAシートの行規定
Const ID As Integer = 1
Const YOUHI As Integer = 13
Const KANPU As Integer = 53
Const FUSOKU As Integer = 54
'========================================
'最終支給台帳シートの列規定
Const SOUSHIKYU As Integer = 64 'BL
Const SHAHO As Integer = 78 'BZ
Const TEIGAKU As Integer = 80 'CB
Const HENDOU As Integer = 81 'CC
Const CHOSYU As Integer = 93 'CO
Const SIMIN As Integer = 94 'CP
Const KOUJYO As Integer = 95 'CQ
Const SASHIHIKI As Integer = 96 'CR
Const ColMax As Integer = 96
'========================================
Dim amt As Long
Dim z As Long, x As Long
Dim colA As Range
Dim v As Variant
Dim ck As Variant
Dim idA As Range
With Sheets("最終支給台帳")
x = .Cells(.Rows.Count, 1).End(xlUp).Row
Set idA = .Range("A2").Resize(x - 1)
.Cells(2, CHOSYU).Resize(x - 1).ClearContents
.Cells(2, KOUJYO).Resize(x - 1).ClearContents
.Cells(2, SASHIHIKI).Resize(x - 1).ClearContents
v = Cells(2, 1).Resize(x - 1, ColMax)
End With
With Sheets("年調DATA")
z = .Cells(1, .Columns.Count).End(xlToLeft).Column
For Each colA In .Range("B1").Resize(54, z - 1).Columns
If colA.Cells(YOUHI).Value = "年末調整する" Then
If colA.Cells(KANPU).Value > 0 Then
amt = colA.Cells(KANPU).Value * -1
Else
amt = colA.Cells(FUSOKU).Value
End If
ck = Application.Match(colA.Cells(ID).Value, idA, 0)
If IsNumeric(ck) Then
v(ck, CHOSYU) = amt
v(ck, KOUJYO) = _
v(ck, SHAHO) + v(ck, TEIGAKU) + v(ck, HENDOU) + v(ck, CHOSYU) + v(ck, SIMIN)
v(ck, SASHIHIKI) = v(ck, SOUSHIKYU) - v(ck, KOUJYO)
End If
End If
Next
End With
Sheets("最終支給台帳").Range("A2").Resize(x - 1, ColMax) = v
Set idA = Nothing
MsgBox "処理が完了しました"
End Sub
|
|
UO3
- 10/12/17(金) 11:33 -
