|
業務日報を作っています。
Worksheets("0")からWorksheets("DF2")に指定した日付内から情報を引き出すように作っています。
同じセル番号同士ではうまくいきますが、違うセルに移す方法が分かりません。
煩雑なソースですが、よろしく御願いいたします。
Private Sub CommandButton1_Click()
If UserForm4.ComboBox1 = "" Or UserForm4.ComboBox12 = "" Or UserForm4.ComboBox9 = "" Or _
UserForm4.ComboBox11 = "" Or UserForm4.ComboBox15 = "" Or UserForm4.ComboBox14 = "" Or _
UserForm4.ComboBox13 = "" Then
MsgBox "検索範囲が不適切です", vbExclamation
Else
'-------閲覧シートへ-------------------
'範囲の取得
at01 = UserForm4.ComboBox1.ListIndex '業務名
'----------開始日-----------------
at02 = UserForm4.ComboBox12.Text '年度
at03 = UserForm4.ComboBox9.Text '月
at04 = UserForm4.ComboBox11.Text '日
'----------終了日-----------------
at05 = UserForm4.ComboBox15.Text '年度
at06 = UserForm4.ComboBox14.Text '月
at07 = UserForm4.ComboBox13.Text '日
'*****************検索*********************
'----------開始日の行-----------------
For i10 = 1 To 1000
If Worksheets("0").Cells(i10, 4).Text = at04 And _
Worksheets("0").Cells(i10, 3).Text = at03 And _
Worksheets("0").Cells(i10, 2).Text = at02 Then
at20 = i10
Exit For
End If
Next i10
'----------終了日の行-----------------
For i20 = 1 To 1000
If Worksheets("0").Cells(i20, 4).Text = at07 And _
Worksheets("0").Cells(i20, 3).Text = at06 And _
Worksheets("0").Cells(i20, 2).Text = at05 Then
at30 = i20
Exit For
End If
Next i20
Worksheets("DF").Range("A11") = at30 '削除用
'*****************表示*********************
'--------------------------------------------
at40 = at30 - at20 '行の範囲
For g20 = at20 To at30
a100 = g20
a110 = "M" + CStr(a100)
a111 = "P" + CStr(a100)
a112 = "A" + CStr(a100)
a113 = "J" + CStr(a100)
a114 = "K" + CStr(a100)
a115 = "R" + CStr(a100)
a116 = "Q" + CStr(a100)
For g30 = 2 To at40 + 2
a100 = g30
a110 = "M" + CStr(a100)
a111 = "P" + CStr(a100)
a112 = "A" + CStr(a100)
a113 = "J" + CStr(a100)
a114 = "K" + CStr(a100)
a115 = "R" + CStr(a100)
a116 = "Q" + CStr(a100)
x1 = Worksheets("0").Range(a110) '業務名
x2 = Worksheets("0").Range(a111) '工期
x3 = Worksheets("0").Range(a112) '社員名
x4 = Worksheets("0").Range(a113) '総時
x5 = Worksheets("0").Range(a114) '総分
x6 = Worksheets("0").Range(a115) '人件費
x7 = Worksheets("0").Range(a116) '契約金額
Worksheets("DF2").Range(a110) = x1 '業務名
Worksheets("DF2").Range(a111) = x2 '工期
Worksheets("DF2").Range(a112) = x3 '社員名
Worksheets("DF2").Range(a113) = x4 '総時
Worksheets("DF2").Range(a114) = x5 '総分
Worksheets("DF2").Range(a115) = x6 '人件費
Worksheets("DF2").Range(a116) = x7 '契約金額
Next g30
Exit For
Next g20
MsgBox "表示が完了しました", vbInformation
UserForm4.Label18.Caption = ""
End If
End Sub
|
|