{"id":4951,"date":"2023-07-06T00:46:33","date_gmt":"2023-07-06T00:46:33","guid":{"rendered":"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/"},"modified":"2023-07-06T00:46:33","modified_gmt":"2023-07-06T00:46:33","slug":"lembar-kerja-loop-vba","status":"publish","type":"post","link":"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/","title":{"rendered":"Cara menelusuri spreadsheet di vba (dengan contoh)"},"content":{"rendered":"

<\/p>\n

\n

Anda bisa menggunakan metode berikut untuk menavigasi lembar di buku kerja Excel menggunakan VBA:<\/span><\/p>\n

Metode 1: Telusuri semua spreadsheet<\/strong><\/span><\/p>\n

 SubLoopSheets<\/span> ()\n\nDim<\/span> ws As<\/span> Worksheet\n\nFor Each<\/span> ws In<\/span> ThisWorkbook.Worksheets\n\n    ws.Range(\"A1\").Value = 100\n\nNext<\/span> ws\n\nEnd Sub\n<\/span><\/strong><\/span><\/pre>\n
 Makro khusus ini menelusuri setiap lembar dalam buku kerja dan menetapkan nilai di sel A1<\/strong> setiap lembar menjadi 100.<\/span><\/p>\n
 Metode 2: Telusuri semua lembar kerja, kecuali lembar kerja tertentu<\/strong><\/span><\/p>\n
 SubLoopSheets<\/span> ()\n\nDim<\/span> ws As<\/span> Worksheet\n\nFor Each<\/span> ws In<\/span> ThisWorkbook.Worksheets\n\n    Select Case<\/span> ws.Name\n        Case Is<\/span> = \"Sheet2\", \"Sheet3\"\n        'Do not execute any code for these sheets<\/span>\n        Case Else<\/span>\n        ws.Range(\"A1\").Value = 100\n    End Select<\/span>\n\nNext<\/span> ws\n\nEnd Sub<\/span><\/strong><\/span><\/pre>\n
 Makro khusus ini mengulang setiap lembar dalam buku kerja dan menetapkan nilai di sel A1<\/strong> setiap lembar menjadi 100, kecuali<\/em> untuk lembar yang disebut Sheet2<\/strong> dan Sheet3<\/strong> .<\/span><\/p>\n
 Contoh berikut memperlihatkan cara menggunakan masing-masing metode ini dalam praktik dengan buku kerja Excel yang berisi empat lembar kosong:<\/span> <\/p>\n
\"\"<\/p>\n
 Contoh 1: Jelajahi semua lembar kerja<\/strong><\/span><\/h2>\n
 Kita bisa menggunakan makro berikut untuk mengatur nilai sel A1<\/strong> pada setiap lembar di buku kerja kita agar sama dengan 100:<\/span><\/p>\n
 SubLoopSheets<\/span> ()\n\nDim<\/span> ws As<\/span> Worksheet\n\nFor Each<\/span> ws In<\/span> ThisWorkbook.Worksheets\n\n    ws.Range(\"A1\").Value = 100\n\nNext<\/span> ws\n<\/strong><\/span><\/pre>\n
 Saat kita menjalankan makro ini, nilai sel A1<\/strong> di setiap lembar buku kerja akan sama dengan 100:<\/span> <\/p>\n
\"\"<\/p>\n
 Contoh 2: Telusuri semua lembar kerja, kecuali lembar kerja tertentu<\/strong><\/span><\/h2>\n
 Katakanlah kita ingin menelusuri setiap lembar kerja dan menetapkan nilai sel A1<\/strong> di setiap lembar menjadi 100, kecuali<\/em> untuk lembar yang disebut Sheet2<\/strong> dan Sheet3<\/strong> .<\/span><\/p>\n
 Kita dapat membuat makro berikut untuk melakukan ini:<\/span><\/p>\n
 SubLoopSheets<\/span> ()\n\nDim<\/span> ws As<\/span> Worksheet\n\nFor Each<\/span> ws In<\/span> ThisWorkbook.Worksheets\n\n    Select Case<\/span> ws.Name\n        Case Is<\/span> = \"Sheet2\", \"Sheet3\"\n        'Do not execute any code for these sheets<\/span>\n        Case Else<\/span>\n        ws.Range(\"A1\").Value = 100\n    End Select<\/span>\n\nNext<\/span> ws\n\nEnd Sub\n<\/span><\/strong><\/span><\/pre>\n
 Saat kita menjalankan makro ini, kita akan melihat bahwa Sheet1<\/strong> dan Sheet4<\/strong> memiliki nilai 100 di sel A1<\/strong> .<\/span><\/p>\n
 Namun, Sheet2<\/strong> dan Sheet3<\/strong> tidak akan memiliki nilai di sel A1<\/strong> karena kita menggunakan fungsi Case<\/strong> untuk mengabaikan sheet berikut:<\/span> <\/p>\n
\"\"<\/p>\n
 Perhatikan bahwa dalam contoh ini, kita telah memeriksa setiap lembar kerja dan menetapkan nilai sel sama dengan nilai tertentu, hanya demi kesederhanaan.<\/span><\/p>\n
 Namun, Anda dapat menggunakan sintaks serupa dengan pernyataan For Each<\/strong> untuk melakukan tugas yang jauh lebih rumit di setiap lembar jika Anda mau.<\/span><\/p>\n
 Sumber daya tambahan<\/strong><\/span><\/h2>\n
 Tutorial berikut menjelaskan cara melakukan tugas umum lainnya di VBA:<\/span><\/p>\n
 VBA: Cara menghitung jumlah baris dalam jangkauan<\/a>
 VBA: Cara menghitung sel dengan teks tertentu<\/a>
 VBA: Cara menulis fungsi COUNTIF dan COUNTIFS<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
Anda bisa menggunakan metode berikut untuk menavigasi lembar di buku kerja Excel menggunakan VBA: Metode 1: Telusuri semua spreadsheet SubLoopSheets () Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Range(“A1”).Value = 100 Next ws End Sub Makro khusus ini menelusuri setiap lembar dalam buku kerja dan menetapkan nilai di sel A1 setiap lembar menjadi […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[],"yoast_head":"\nCara Menelusuri Spreadsheet di VBA (dengan Contoh) - Statologi<\/title>\n<meta name=\"description\" content=\"Tutorial ini menjelaskan cara menavigasi spreadsheet dan melakukan tugas tertentu menggunakan VBA, dengan beberapa contoh.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/\" \/>\n<meta property=\"og:locale\" content=\"id_ID\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Cara Menelusuri Spreadsheet di VBA (dengan Contoh) - Statologi\" \/>\n<meta property=\"og:description\" content=\"Tutorial ini menjelaskan cara menavigasi spreadsheet dan melakukan tugas tertentu menggunakan VBA, dengan beberapa contoh.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"Statorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-06T00:46:33+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/boucle1.png\" \/>\n<meta name=\"author\" content=\"Benjamin anderson\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Ditulis oleh\" \/>\n\t<meta name=\"twitter:data1\" content=\"Benjamin anderson\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimasi waktu membaca\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 menit\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/\",\"url\":\"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/\",\"name\":\"Cara Menelusuri Spreadsheet di VBA (dengan Contoh) - Statologi\",\"isPartOf\":{\"@id\":\"https:\/\/statorials.org\/id\/#website\"},\"datePublished\":\"2023-07-06T00:46:33+00:00\",\"dateModified\":\"2023-07-06T00:46:33+00:00\",\"author\":{\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\"},\"description\":\"Tutorial ini menjelaskan cara menavigasi spreadsheet dan melakukan tugas tertentu menggunakan VBA, dengan beberapa contoh.\",\"breadcrumb\":{\"@id\":\"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/#breadcrumb\"},\"inLanguage\":\"id\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/statorials.org\/id\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cara menelusuri spreadsheet di vba (dengan contoh)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/statorials.org\/id\/#website\",\"url\":\"https:\/\/statorials.org\/id\/\",\"name\":\"Statorials\",\"description\":\"Panduan anda untuk kompetensi statistik!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/statorials.org\/id\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"id\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\",\"name\":\"Benjamin anderson\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"id\",\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg\",\"contentUrl\":\"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg\",\"caption\":\"Benjamin anderson\"},\"description\":\"Halo, saya Benjamin, pensiunan profesor statistika yang menjadi guru Statorial yang berdedikasi. Dengan pengalaman dan keahlian yang luas di bidang statistika, saya ingin berbagi ilmu untuk memberdayakan mahasiswa melalui Statorials. Baca selengkapnya\",\"sameAs\":[\"http:\/\/statorials.org\/id\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Cara Menelusuri Spreadsheet di VBA (dengan Contoh) - Statologi","description":"Tutorial ini menjelaskan cara menavigasi spreadsheet dan melakukan tugas tertentu menggunakan VBA, dengan beberapa contoh.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/","og_locale":"id_ID","og_type":"article","og_title":"Cara Menelusuri Spreadsheet di VBA (dengan Contoh) - Statologi","og_description":"Tutorial ini menjelaskan cara menavigasi spreadsheet dan melakukan tugas tertentu menggunakan VBA, dengan beberapa contoh.","og_url":"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/","og_site_name":"Statorials","article_published_time":"2023-07-06T00:46:33+00:00","og_image":[{"url":"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/boucle1.png"}],"author":"Benjamin anderson","twitter_card":"summary_large_image","twitter_misc":{"Ditulis oleh":"Benjamin anderson","Estimasi waktu membaca":"2 menit"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/","url":"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/","name":"Cara Menelusuri Spreadsheet di VBA (dengan Contoh) - Statologi","isPartOf":{"@id":"https:\/\/statorials.org\/id\/#website"},"datePublished":"2023-07-06T00:46:33+00:00","dateModified":"2023-07-06T00:46:33+00:00","author":{"@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81"},"description":"Tutorial ini menjelaskan cara menavigasi spreadsheet dan melakukan tugas tertentu menggunakan VBA, dengan beberapa contoh.","breadcrumb":{"@id":"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/#breadcrumb"},"inLanguage":"id","potentialAction":[{"@type":"ReadAction","target":["https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/statorials.org\/id\/lembar-kerja-loop-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/statorials.org\/id\/"},{"@type":"ListItem","position":2,"name":"Cara menelusuri spreadsheet di vba (dengan contoh)"}]},{"@type":"WebSite","@id":"https:\/\/statorials.org\/id\/#website","url":"https:\/\/statorials.org\/id\/","name":"Statorials","description":"Panduan anda untuk kompetensi statistik!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/statorials.org\/id\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"id"},{"@type":"Person","@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81","name":"Benjamin anderson","image":{"@type":"ImageObject","inLanguage":"id","@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/image\/","url":"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg","contentUrl":"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg","caption":"Benjamin anderson"},"description":"Halo, saya Benjamin, pensiunan profesor statistika yang menjadi guru Statorial yang berdedikasi. Dengan pengalaman dan keahlian yang luas di bidang statistika, saya ingin berbagi ilmu untuk memberdayakan mahasiswa melalui Statorials. Baca selengkapnya","sameAs":["http:\/\/statorials.org\/id"]}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/4951"}],"collection":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/comments?post=4951"}],"version-history":[{"count":0,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/4951\/revisions"}],"wp:attachment":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/media?parent=4951"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/categories?post=4951"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/tags?post=4951"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}