入力シートと保存シートに分け、保存シートを関数で参照する。
登録時や起動時に常に関数の参照範囲を変更する。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
Sub reset() Sheets("新規").Rows("2:1000").Delete Sheets("更新").Rows("2:1000").Delete End Sub Sub style() '新規 Sheets("新規").Columns("A").NumberFormatLocal = "yyyy/mm/dd" Sheets("新規").Columns("B").NumberFormatLocal = "0" Sheets("新規").Columns("C").NumberFormatLocal = "@" Sheets("新規").Cells(1, 1).Value = "日付" Sheets("新規").Cells(1, 2).Value = "人数" Sheets("新規").Cells(1, 3).Value = "カード番号" Sheets("新規").Columns("A:C").AutoFit '更新 Sheets("更新").Columns("A").NumberFormatLocal = "yyyy/mm/dd" Sheets("更新").Columns("B").NumberFormatLocal = "0" Sheets("更新").Cells(1, 1).Value = "日付" Sheets("更新").Cells(1, 2).Value = "人数" Sheets("更新").Columns("A:B").AutoFit '表紙 Sheets("表紙").Range("B6:B15").NumberFormatLocal = "@" Sheets("表紙").Cells(3, 2).NumberFormatLocal = "yyyy/mm/dd" Sheets("表紙").Cells(3, 4).NumberFormatLocal = "0" Sheets("表紙").Cells(6, 4).Value = Sheets("新規").UsedRange.Rows.Count Sheets("表紙").Cells(7, 4).Value = Sheets("更新").UsedRange.Rows.Count Dim btm(2) btm(0) = Sheets("表紙").Cells(6, 4).Value btm(1) = Sheets("表紙").Cells(7, 4).Value If btm(0) = 1 Then btm(0) = 2 If btm(1) = 1 Then btm(1) = 2 For r = 3 To 33 Sheets("表紙").Cells(r, 9).Value = "=SUMIFS(新規!B2:B" & btm(0) & ",新規!A2:A" & btm(0) & ",表紙!G" & r & ")" Sheets("表紙").Cells(r, 10).Value = "=SUMIFS(更新!B2:B" & btm(1) & ",更新!A2:A" & btm(1) & ",表紙!G" & r & ")" Next r End Sub Sub auto_open() Call style Sheets("表紙").Cells(3, 2).Value = Date End Sub Sub main() Dim btm(2) btm(0) = Sheets("表紙").Cells(6, 4).Value + 1 btm(1) = Sheets("表紙").Cells(7, 4).Value + 1 '新規 Dim tmp As String For r = 6 To 15 tmp = Sheets("表紙").Cells(r, 2).Value If tmp <> "" Then Sheets("新規").Cells(btm(0), 1).Value = Sheets("表紙").Cells(3, 2).Value Sheets("新規").Cells(btm(0), 2).Value = 1 tmp = "27" & Right("000000000000000000" & tmp, 18) Sheets("新規").Cells(btm(0), 3).Value = tmp btm(0) = btm(0) + 1 End If Next r Sheets("表紙").Range("B6:B15").Value = "" '更新 If Sheets("表紙").Cells(3, 4).Value <> "" And IsNumeric(Sheets("表紙").Cells(3, 4).Value) Then Sheets("更新").Cells(btm(1), 1).Value = Sheets("表紙").Cells(3, 2).Value Sheets("更新").Cells(btm(1), 2).Value = Sheets("表紙").Cells(3, 4).Value End If Sheets("表紙").Cells(3, 4).Value = "" Call style MsgBox "終了しました。" End Sub |