{"id":3346,"date":"2020-05-01T03:36:14","date_gmt":"2020-04-30T18:36:14","guid":{"rendered":"https:\/\/okamurax.com\/?p=3346"},"modified":"2020-05-01T03:36:14","modified_gmt":"2020-04-30T18:36:14","slug":"vba-%e4%bb%95%e5%85%a5%e3%82%b7%e3%83%bc%e3%83%88%e3%82%b3%e3%83%94%e3%83%bc%e3%83%bb%e3%83%9e%e3%82%b9%e3%82%bf%e3%83%81%e3%82%a7%e3%83%83%e3%82%af","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=3346","title":{"rendered":"VBA \u4ed5\u5165\u30b7\u30fc\u30c8\u30b3\u30d4\u30fc\u30fb\u30de\u30b9\u30bf\u30c1\u30a7\u30c3\u30af"},"content":{"rendered":"

\u4ee5\u524d\u5229\u7528\u3057\u3066\u3044\u305fVBA<\/p>\n

Sub Cp()\r\nApplication.ScreenUpdating = False\r\nApplication.EnableEvents = False\r\nApplication.Calculation = xlCalculationManual\r\nFor Each w In Sheets\r\n    If w.Name = \"\u96c6\u8a08\" Then Sheets(\"\u96c6\u8a08\").Delete\r\nNext w\r\nSheets.Add Before:=Sheets(1)\r\nActiveSheet.Name = \"\u96c6\u8a08\"\r\nFor Each w In Worksheets\r\n    If w.Name <> \"\u96c6\u8a08\" And w.Name <> \"\u6ce8\u6587\u66f8\" And w.Name <> \"\u30de\u30b9\u30bf\" Then\r\n        If Sheets(\"\u96c6\u8a08\").UsedRange.Rows.Count = 1 Then\r\n            r = 1\r\n        Else\r\n            r = Sheets(\"\u96c6\u8a08\").UsedRange.Rows.Count + 1\r\n        End If\r\n        w.UsedRange.Copy\r\n        Sheets(\"\u96c6\u8a08\").Cells(r, 1).PasteSpecial Paste:=xlPasteValues\r\n        Sheets(\"\u96c6\u8a08\").Cells(r, 1).PasteSpecial Paste:=xlPasteFormats\r\n        Application.CutCopyMode = False\r\n        w.UsedRange.ClearContents\r\n    End If\r\nNext w\r\nc = 16 '\u6750\u6599\/\u5916\u6ce8 = 19, \u904b\u9001=16\r\nk = 2\r\nFor i = 1 To Sheets(\"\u96c6\u8a08\").UsedRange.Rows.Count\r\n    Application.StatusBar = \"\u91cd\u8907\u524a\u9664\u4e2d ... \" & i\r\n    If Sheets(\"\u96c6\u8a08\").Cells(k, c).Value = \"\" Or Sheets(\"\u96c6\u8a08\").Cells(k, c).Value = 0 Or IsNumeric(Sheets(\"\u96c6\u8a08\").Cells(k, c).Value) = False Then\r\n        Sheets(\"\u96c6\u8a08\").Rows(k).Delete\r\n    Else\r\n        k = k + 1\r\n    End If\r\nNext i\r\nFor Each w In Sheets\r\n    If w.Name <> \"\u96c6\u8a08\" Then w.Delete\r\nNext w\r\nApplication.StatusBar = False\r\nApplication.ScreenUpdating = True\r\nApplication.EnableEvents = True\r\nApplication.Calculation = xlCalculationAutomatic\r\nMsgBox \"done\"\r\nEnd Sub<\/pre>\n
Dim supRange As Range\r\n\r\nSub MainLoop()\r\n\r\n' \u30a2\u30af\u30c6\u30a3\u30d6\u30b7\u30fc\u30c8\u3067\u52d5\u4f5c\u3059\u308b\u306e\u3067\u6ce8\u610f\r\n\r\nCall SetMasterRange\r\n\r\nSet w = ActiveSheet\r\n\r\nr = 3\r\nDo While w.Cells(r, 2).Value <> \"\"\r\n\r\n  If MasterLoop(w.Cells(r, 2).Value, w.Cells(r, 3).Value) = False Then\r\n    MsgBox r & \"\u884c\u306b\u30a8\u30e9\u30fc\u304c\u3042\u308a\u307e\u3059\u3002\"\r\n    Exit Sub\r\n  End If\r\n\r\nr = r + 1\r\nLoop\r\n\r\nEnd Sub\r\n\r\nSub SetMasterRange()\r\n\r\n' \u884c\u672b\u30cf\u30fc\u30c9\u30b3\u30fc\u30c7\u30a3\u30f3\u30b0\u3057\u3066\u3044\u308b\u306e\u3067\u6ce8\u610f\r\n\r\nSet w = Sheets(\"\u30de\u30b9\u30bf\")\r\nSet supRange = w.Range(\"B2:C121\")\r\n\r\nEnd Sub\r\n\r\nFunction MasterLoop(supCode, supName)\r\n\r\nFor r = 2 To supRange.Rows.Count\r\n\r\n  If supRange.Cells(r, 3).Value = supName And supRange.Cells(r, 2).Value = supCode Then\r\n    MasterLoop = True\r\n    Exit Function\r\n  End If\r\n  \r\nNext\r\n\r\nMasterLoop = False\r\n\r\nEnd Function<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
\u4ee5\u524d\u5229\u7528\u3057\u3066\u3044\u305fVBA Sub Cp() Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculati … <\/p>\n
“VBA \u4ed5\u5165\u30b7\u30fc\u30c8\u30b3\u30d4\u30fc\u30fb\u30de\u30b9\u30bf\u30c1\u30a7\u30c3\u30af” \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-3346","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\/3346","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=3346"}],"version-history":[{"count":1,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/3346\/revisions"}],"predecessor-version":[{"id":3347,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/3346\/revisions\/3347"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3346"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}