{"id":1315,"date":"2017-05-09T04:37:56","date_gmt":"2017-05-08T19:37:56","guid":{"rendered":"http:\/\/okamurax.com\/?p=1315"},"modified":"2017-05-09T14:28:47","modified_gmt":"2017-05-09T05:28:47","slug":"vba-%e5%8d%b3%e5%b8%ad%e4%bc%9a%e5%93%a1%e6%95%b0%e4%bf%9d%e5%ad%98","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=1315","title":{"rendered":"VBA \u5373\u5e2d\u4f1a\u54e1\u6570\u4fdd\u5b58"},"content":{"rendered":"<p>\u5165\u529b\u30b7\u30fc\u30c8\u3068\u4fdd\u5b58\u30b7\u30fc\u30c8\u306b\u5206\u3051\u3001\u4fdd\u5b58\u30b7\u30fc\u30c8\u3092\u95a2\u6570\u3067\u53c2\u7167\u3059\u308b\u3002<br \/>\n\u767b\u9332\u6642\u3084\u8d77\u52d5\u6642\u306b\u5e38\u306b\u95a2\u6570\u306e\u53c2\u7167\u7bc4\u56f2\u3092\u5909\u66f4\u3059\u308b\u3002<\/p>\n<pre class=\"lang:vb decode:true \">Sub reset()\r\n\r\nSheets(\"\u65b0\u898f\").Rows(\"2:1000\").Delete\r\nSheets(\"\u66f4\u65b0\").Rows(\"2:1000\").Delete\r\n\r\nEnd Sub\r\n\r\nSub style()\r\n\r\n'\u65b0\u898f\r\nSheets(\"\u65b0\u898f\").Columns(\"A\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nSheets(\"\u65b0\u898f\").Columns(\"B\").NumberFormatLocal = \"0\"\r\nSheets(\"\u65b0\u898f\").Columns(\"C\").NumberFormatLocal = \"@\"\r\n\r\nSheets(\"\u65b0\u898f\").Cells(1, 1).Value = \"\u65e5\u4ed8\"\r\nSheets(\"\u65b0\u898f\").Cells(1, 2).Value = \"\u4eba\u6570\"\r\nSheets(\"\u65b0\u898f\").Cells(1, 3).Value = \"\u30ab\u30fc\u30c9\u756a\u53f7\"\r\n\r\nSheets(\"\u65b0\u898f\").Columns(\"A:C\").AutoFit\r\n\r\n'\u66f4\u65b0\r\nSheets(\"\u66f4\u65b0\").Columns(\"A\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nSheets(\"\u66f4\u65b0\").Columns(\"B\").NumberFormatLocal = \"0\"\r\n\r\nSheets(\"\u66f4\u65b0\").Cells(1, 1).Value = \"\u65e5\u4ed8\"\r\nSheets(\"\u66f4\u65b0\").Cells(1, 2).Value = \"\u4eba\u6570\"\r\n\r\nSheets(\"\u66f4\u65b0\").Columns(\"A:B\").AutoFit\r\n\r\n'\u8868\u7d19\r\nSheets(\"\u8868\u7d19\").Range(\"B6:B15\").NumberFormatLocal = \"@\"\r\nSheets(\"\u8868\u7d19\").Cells(3, 2).NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nSheets(\"\u8868\u7d19\").Cells(3, 4).NumberFormatLocal = \"0\"\r\n\r\nSheets(\"\u8868\u7d19\").Cells(6, 4).Value = Sheets(\"\u65b0\u898f\").UsedRange.Rows.Count\r\nSheets(\"\u8868\u7d19\").Cells(7, 4).Value = Sheets(\"\u66f4\u65b0\").UsedRange.Rows.Count\r\n\r\nDim btm(2)\r\nbtm(0) = Sheets(\"\u8868\u7d19\").Cells(6, 4).Value\r\nbtm(1) = Sheets(\"\u8868\u7d19\").Cells(7, 4).Value\r\nIf btm(0) = 1 Then btm(0) = 2\r\nIf btm(1) = 1 Then btm(1) = 2\r\nFor r = 3 To 33\r\n  Sheets(\"\u8868\u7d19\").Cells(r, 9).Value = \"=SUMIFS(\u65b0\u898f!B2:B\" &amp; btm(0) &amp; \",\u65b0\u898f!A2:A\" &amp; btm(0) &amp; \",\u8868\u7d19!G\" &amp; r &amp; \")\"\r\n  Sheets(\"\u8868\u7d19\").Cells(r, 10).Value = \"=SUMIFS(\u66f4\u65b0!B2:B\" &amp; btm(1) &amp; \",\u66f4\u65b0!A2:A\" &amp; btm(1) &amp; \",\u8868\u7d19!G\" &amp; r &amp; \")\"\r\nNext r\r\n\r\nEnd Sub\r\n\r\nSub auto_open()\r\n\r\nCall style\r\n\r\nSheets(\"\u8868\u7d19\").Cells(3, 2).Value = Date\r\n\r\nEnd Sub\r\n\r\nSub main()\r\n\r\nDim btm(2)\r\nbtm(0) = Sheets(\"\u8868\u7d19\").Cells(6, 4).Value + 1\r\nbtm(1) = Sheets(\"\u8868\u7d19\").Cells(7, 4).Value + 1\r\n\r\n'\u65b0\u898f\r\nDim tmp As String\r\nFor r = 6 To 15\r\n  tmp = Sheets(\"\u8868\u7d19\").Cells(r, 2).Value\r\n  If tmp &lt;&gt; \"\" Then\r\n    Sheets(\"\u65b0\u898f\").Cells(btm(0), 1).Value = Sheets(\"\u8868\u7d19\").Cells(3, 2).Value\r\n    Sheets(\"\u65b0\u898f\").Cells(btm(0), 2).Value = 1\r\n    tmp = \"27\" &amp; Right(\"000000000000000000\" &amp; tmp, 18)\r\n    Sheets(\"\u65b0\u898f\").Cells(btm(0), 3).Value = tmp\r\n    btm(0) = btm(0) + 1\r\n  End If\r\nNext r\r\n\r\nSheets(\"\u8868\u7d19\").Range(\"B6:B15\").Value = \"\"\r\n\r\n'\u66f4\u65b0\r\nIf Sheets(\"\u8868\u7d19\").Cells(3, 4).Value &lt;&gt; \"\" And IsNumeric(Sheets(\"\u8868\u7d19\").Cells(3, 4).Value) Then\r\n  Sheets(\"\u66f4\u65b0\").Cells(btm(1), 1).Value = Sheets(\"\u8868\u7d19\").Cells(3, 2).Value\r\n  Sheets(\"\u66f4\u65b0\").Cells(btm(1), 2).Value = Sheets(\"\u8868\u7d19\").Cells(3, 4).Value\r\nEnd If\r\n\r\nSheets(\"\u8868\u7d19\").Cells(3, 4).Value = \"\"\r\n\r\nCall style\r\n\r\nMsgBox \"\u7d42\u4e86\u3057\u307e\u3057\u305f\u3002\"\r\n\r\nEnd Sub\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5165\u529b\u30b7\u30fc\u30c8\u3068\u4fdd\u5b58\u30b7\u30fc\u30c8\u306b\u5206\u3051\u3001\u4fdd\u5b58\u30b7\u30fc\u30c8\u3092\u95a2\u6570\u3067\u53c2\u7167\u3059\u308b\u3002 \u767b\u9332\u6642\u3084\u8d77\u52d5\u6642\u306b\u5e38\u306b\u95a2\u6570\u306e\u53c2\u7167\u7bc4\u56f2\u3092\u5909\u66f4\u3059\u308b\u3002 Sub reset() Sheets(&#8220;\u65b0\u898f&#8221;).Rows(&#8220;2:1000&#8221;).Delete Sheets(&#8220;\u66f4 &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/appbay.org\/?p=1315\" class=\"more-link\"><span class=\"screen-reader-text\">&#8220;VBA \u5373\u5e2d\u4f1a\u54e1\u6570\u4fdd\u5b58&#8221; \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[5],"class_list":["post-1315","post","type-post","status-publish","format-standard","hentry","category-1","tag-vba"],"_links":{"self":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1315","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1315"}],"version-history":[{"count":3,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1315\/revisions"}],"predecessor-version":[{"id":1319,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1315\/revisions\/1319"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1315"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1315"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1315"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}