質問編集履歴
1
全体のコードに修正しました。見づらくてすみません。
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -26,9 +26,18 @@
|
|
|
26
26
|
### 該当のソースコード
|
|
27
27
|
|
|
28
28
|
```VBA
|
|
29
|
+
Sub CommandButton1_Click()
|
|
30
|
+
|
|
31
|
+
Dim WEM As String
|
|
32
|
+
Dim Pa As String
|
|
29
|
-
Dim i
|
|
33
|
+
Dim i As Long
|
|
30
|
-
Dim j
|
|
34
|
+
Dim j As Long
|
|
35
|
+
|
|
36
|
+
|
|
37
|
+
WEM = CLng(DateValue(TextBox1.Value)) & "WE"
|
|
38
|
+
Pa = CLng(DateValue(TextBox1.Value)) & "a"
|
|
39
|
+
|
|
31
|
-
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
|
|
40
|
+
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
|
|
32
41
|
For j = 5 To 10
|
|
33
42
|
If Worksheets("form1").Cells(i, j).Value = "" Then
|
|
34
43
|
MsgBox "form1に抜けがあります。正しく入力してください。"
|
|
@@ -37,8 +46,19 @@
|
|
|
37
46
|
Next
|
|
38
47
|
Next
|
|
39
48
|
|
|
49
|
+
On Error GoTo ErrorHandler '日付が正しく入力されないとエラーになるので ErrorHandlerに飛ぶ
|
|
50
|
+
MsgBox "報告全数 : " & WorksheetFunction.SumIf(Range("b:b"), Cells(2, 11), Range("d:d")) & vbCrLf & _
|
|
51
|
+
"出席者数 : " & _
|
|
52
|
+
WorksheetFunction.VLookup(WEM, Worksheets("form2").Range("C:J"), 8, False) & vbCrLf & _
|
|
53
|
+
"----------------------------" & vbCrLf & _
|
|
54
|
+
"Paの数 : " & WorksheetFunction.VLookup(Pa, Range("A:I"), 4, False) & vbCrLf & _
|
|
55
|
+
"Pa1 : " & WorksheetFunction.VLookup(Pa, Range("A:I"), 5, False) & vbCrLf & _
|
|
56
|
+
"Pa2 : " & WorksheetFunction.VLookup(Pa, Range("A:I"), 6, False) & vbCrLf & _
|
|
57
|
+
"Pa3 : " & WorksheetFunction.VLookup(Pa, Range("A:I"), 7, False) & vbCrLf & _
|
|
58
|
+
"Pa4 : " & WorksheetFunction.VLookup(Pa, Range("A:I"), 8, False) & vbCrLf & _
|
|
59
|
+
"Pa5 : " & WorksheetFunction.VLookup(Pa, Range("A:I"), 9, False), vbOKOnly, “ Pa " & Format(TextBox1.Value, "yyyy年mm月")
|
|
60
|
+
Exit Sub
|
|
61
|
+
ErrorHandler:
|
|
62
|
+
MsgBox "年月日が正しく入力されませんでした"
|
|
63
|
+
End Sub
|
|
40
64
|
|
|
41
|
-
|
|
42
|
-
|
|
43
|
-
|
|
44
|
-
|