{"id":1784,"date":"2017-07-27T09:38:36","date_gmt":"2017-07-27T00:38:36","guid":{"rendered":"http:\/\/okamurax.com\/?p=1784"},"modified":"2017-07-27T09:38:36","modified_gmt":"2017-07-27T00:38:36","slug":"vba-%e9%83%a8%e5%93%81%e7%ae%a1%e7%90%86%e7%b0%a1%e6%98%93%e3%83%9e%e3%82%af%e3%83%ad","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=1784","title":{"rendered":"VBA \u90e8\u54c1\u7ba1\u7406\u7c21\u6613\u30de\u30af\u30ed"},"content":{"rendered":"

\u3061\u3087\u3063\u3068\u3057\u305f\u90e8\u54c1\u7ba1\u7406\u7528\u30b7\u30fc\u30c8\u3002<\/p>\n

Sub SetStyle()\r\n\r\nSheets(\"\u96c6\u8a08\").Rows(\"2:1000\").Delete\r\nSheets(\"\u5fc5\u8981\u6570\").Range(\"F2:F18\").Value = \"\"\r\nSheets(\"\u5fc5\u8981\u6570\").Range(\"H2:H18\").Value = \"\"\r\nSheets(\"\u5fc5\u8981\u6570\").Range(\"J2:J18\").Value = \"\"\r\nSheets(\"\u5fc5\u8981\u6570\").Range(\"L2:L18\").Value = \"\"\r\n\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton1_Click()\r\n\r\nyn = MsgBox(\"\u96c6\u8a08\u30b7\u30fc\u30c8\u306f\u30af\u30ea\u30a2\u3055\u308c\u307e\u3059\u3002\u3088\u308d\u3057\u3044\u3067\u3059\u304b\uff1f\", vbYesNo)\r\nIf yn = vbNo Then Exit Sub\r\n\r\nCall SetStyle\r\n\r\nSet w = Sheets(\"\u30ea\u30b9\u30c8\")\r\n\r\nr = 2\r\nDo While w.Cells(r, 2).Value <> \"\"\r\n\r\n  modelName = w.Cells(r, 2).Value\r\n  qty = w.Cells(r, 4).Value\r\n  \r\n  Call RowsCopy(modelName, qty)\r\n\r\nr = r + 1\r\nLoop\r\n\r\nCall MakeSummary\r\n\r\nSheets(\"\u96c6\u8a08\").Cells(1, 1).CurrentRegion.Borders.LineStyle = True\r\n\r\nSheets(\"\u5fc5\u8981\u6570\").Activate\r\n\r\nEnd Sub\r\n\r\nSub RowsCopy(modelName, qty)\r\n\r\nSet w = Sheets(\"\u90e8\u54c1\")\r\n\r\ndestRow = Sheets(\"\u96c6\u8a08\").UsedRange.Rows.Count + 1\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value <> \"\"\r\n\r\n  If w.Cells(r, 1).Value = modelName Then\r\n  \r\n    w.Range(\"A\" & r & \":G\" & r & \"\").Copy Sheets(\"\u96c6\u8a08\").Range(\"A\" & destRow & \":G\" & destRow & \"\")\r\n    Sheets(\"\u96c6\u8a08\").Range(\"G\" & destRow & \"\").Value = Sheets(\"\u96c6\u8a08\").Range(\"G\" & destRow & \"\") * qty\r\n    Sheets(\"\u96c6\u8a08\").Range(\"H\" & destRow & \"\").Value = \"=E\" & destRow & \"*F\" & destRow & \"*G\" & destRow & \"\"\r\n    \r\n    destRow = destRow + 1\r\n  \r\n  End If\r\n\r\nr = r + 1\r\nLoop\r\n\r\nEnd Sub\r\n\r\nSub MakeSummary()\r\n\r\nSet w = Sheets(\"\u5fc5\u8981\u6570\")\r\n\r\nlastRow = Sheets(\"\u96c6\u8a08\").UsedRange.Rows.Count\r\n\r\nFor r = 2 To 18\r\n\r\n  w.Cells(r, 3).Value = \"=SUMIFS(\u96c6\u8a08!H2:H\" & lastRow & \",\u96c6\u8a08!C2:C\" & lastRow & \",A\" & r & \",\u96c6\u8a08!D2:D\" & lastRow & \",B\" & r & \")\"\r\n  w.Cells(r, 4).Value = \"=SUMIFS(\u96c6\u8a08!G2:G\" & lastRow & \",\u96c6\u8a08!C2:C\" & lastRow & \",A\" & r & \",\u96c6\u8a08!D2:D\" & lastRow & \",B\" & r & \")\"\r\n\r\nNext r\r\n\r\nEnd Sub<\/pre>\n","protected":false},"excerpt":{"rendered":"
\u3061\u3087\u3063\u3068\u3057\u305f\u90e8\u54c1\u7ba1\u7406\u7528\u30b7\u30fc\u30c8\u3002 Sub SetStyle() Sheets(“\u96c6\u8a08”).Rows(“2:1000”).Delete Sheets(“\u5fc5\u8981\u6570”).Range(“F2:F18”).Value = “” She … <\/p>\n
“VBA \u90e8\u54c1\u7ba1\u7406\u7c21\u6613\u30de\u30af\u30ed” \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-1784","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\/1784","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=1784"}],"version-history":[{"count":1,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1784\/revisions"}],"predecessor-version":[{"id":1785,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1784\/revisions\/1785"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1784"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1784"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1784"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}