{"id":2146,"date":"2023-09-28T22:43:00","date_gmt":"2023-09-28T22:43:00","guid":{"rendered":"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/"},"modified":"2023-12-06T05:50:29","modified_gmt":"2023-12-06T05:50:29","slug":"loop-door-het-bereik","status":"publish","type":"page","link":"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/","title":{"rendered":"Hoe navigeer je door een bereik met vba?"},"content":{"rendered":"<div class=\"excel-voor-de-inhoud\" id=\"excel-3902213832\"><script type=\"text\/javascript\">\r\n\tatOptions = {\r\n\t\t'key' : 'c1158f160081d6540a8409e6925dab94',\r\n\t\t'format' : 'iframe',\r\n\t\t'height' : 250,\r\n\t\t'width' : 300,\r\n\t\t'params' : {}\r\n\t};\r\n<\/script>\r\n<script type=\"text\/javascript\" src=\"\/\/www.highperformanceformat.com\/c1158f160081d6540a8409e6925dab94\/invoke.js\"><\/script><\/div> <p>Om een bereik in VBA te doorlopen, moet je de optie &#8220;For Each Loop&#8221; gebruiken. Met behulp van deze lus kunt u code schrijven waarin u VBA vertelt elke cel in het bereik, de kolom of de rij te doorlopen en een specifieke activiteit uit te voeren.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\"><strong>Sub<\/strong> vba_loop_range() <strong>Dim<\/strong> iCell <strong>As<\/strong> Range <strong>For<\/strong> <strong>Each<\/strong> iCell <strong>In<\/strong> Range(\"A1:A10\").Cells   iCell.Value = \"Yes\" <strong>Next<\/strong> iCell <strong>End<\/strong> <strong>Sub<\/strong><\/code><\/pre><p> Hier zijn de stappen om over een strand te lopen:<\/p><ol><li> Declareer eerst een variabele die u wilt gebruiken als de cel van het bereik waarover u een lus wilt maken. <br><img loading=\"lazy\" decoding=\"async\" title=\"1-doorlusbereik\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-boucle-a-travers-la-gamme.png\" sizes=\"auto, \" srcset=\"\" alt=\"loop-through-range\" width=\"281\" height=\"277\"><\/li><li> Start daarna de lus &#8220;For Each&#8221; en gebruik de variabele &#8220;iCell&#8221; om elke cel in het bereik tijdens de lus weer te geven. <br><img loading=\"lazy\" decoding=\"async\" title=\"2-gebruik-icell-variabele\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/2-utiliser-la-variable-icecell.png\" sizes=\"auto, \" srcset=\"\" alt=\"use-icell-variable\" width=\"404\" height=\"289\"><\/li><li> Vervolgens moet u een regel code schrijven om een activiteit uit te voeren op elke cel waarin we het bereik herhalen. <br><img loading=\"lazy\" decoding=\"async\" title=\"3-schrijf-een-regel-code\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/3-ecrire-une-ligne-de-code.png\" sizes=\"auto, \" srcset=\"\" alt=\"write-a-line-of-code\" width=\"420\" height=\"356\"><\/li><li> Be\u00ebindig aan het einde de code voor een lus. <br><img loading=\"lazy\" decoding=\"async\" title=\"4-be\u00ebindig-de-code-voor-een-lus\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/4-terminer-le-code-pour-une-boucle.png\" sizes=\"auto, \" srcset=\"\" alt=\"end-the-code-for-a-loop\" width=\"418\" height=\"363\"><\/li><\/ol><h2 class=\"wp-block-heading\"> Loop over de hele kolom en \u00e9\u00e9n rij met VBA<\/h2><p> Op dezelfde manier kunt u elke cel in een rij doorlopen met behulp van de volgende code die identiek is aan de code die we in het vorige voorbeeld hebben gebruikt. Het enige verschil is het bereik dat we hebben opgegeven. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-61538\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/5-boucles-sur-toute-la-colonne-et-la-ligne.png\" sizes=\"auto, \" srcset=\"\" alt=\"loop-door-de-hele-kolom-en-rij\" width=\"406\" height=\"344\"><\/figure><\/div><p> Opmerking: voordat u een hele kolom of rij doorloopt, moet u begrijpen dat de lus enige tijd kan duren en dat uw Excel een paar minuten kan vastlopen.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\"><strong>Sub<\/strong> vba_loop_range() <strong>Dim<\/strong> iCell <strong>As<\/strong> Range <strong>For<\/strong> <strong>Each<\/strong> iCell <strong>In<\/strong> Range(\"A:A\").Cells   iCell.Value = \"Yes\" <strong>Next<\/strong> iCell <strong>End<\/strong> <strong>Sub<\/strong><\/code><\/pre><p> En op dezelfde manier kunt u een volledige lijn gebruiken als bereik.<\/p><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\"><strong>Sub<\/strong> vba_loop_range() <strong>Dim<\/strong> iCell <strong>As<\/strong> Range <strong>For<\/strong> <strong>Each<\/strong> iCell <strong>In<\/strong> Range(\"1:1\").Cells   iCell.Value = \"Yes\" <strong>Next<\/strong> iCell <strong>End<\/strong> <strong>Sub<\/strong><\/code><\/pre><h2 class=\"wp-block-heading\"> Loop door het dynamisch bereik<\/h2><p> En als u een dynamisch celbereik wilt herhalen, raadpleegt u de volgende code. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-61539\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/6-boucles-a-travers-la-plage-dynamique.png\" sizes=\"auto, \" srcset=\"\" alt=\"loop door het dynamisch bereik\" width=\"751\" height=\"469\"><\/figure><\/div><pre class=\"wp-block-code\"> <code class=\"language-visual-basic\" lang=\"visual-basic\"><strong>Sub<\/strong> vba_dynamic_loop_range() <strong>Dim<\/strong> iCell <strong>As<\/strong> Range <strong>Dim<\/strong> iRange1 <strong>As<\/strong> String <strong>Dim<\/strong> iRange2 <strong>As<\/strong> String <strong>Dim<\/strong> rangeName <strong>As<\/strong> String iRange1 = ActiveCell.Address iRange2 = ActiveCell.Offset(5, 5).Address rangeName = iRange1 &amp; \":\" &amp; iRange2 <strong>For<\/strong> <strong>Each<\/strong> iCell <strong>In<\/strong> Range(rangeName).Cells   iCell = \"Yes\" <strong>Next<\/strong> iCell <strong>End<\/strong> <strong>Sub<\/strong><\/code><\/pre><div class=\"gb-container gb-container-4db6181c gb-container-mt\"> <a class=\"gb-button gb-button-c5897111 gb-button-text gb-button-mt-button\" href=\"https:\/\/exceladvisor.org\/nl\/vba\/\">Wat is VBA<\/a><h2 class=\"gb-headline gb-headline-665e5f6b gb-headline-text gb-headline-mt-heading\"> Gerelateerde tutorials <\/h2><div class=\"gb-grid-wrapper gb-grid-wrapper-a20baf7c gb-grid-wrapper-mt-grid\"><div class=\"gb-grid-column gb-grid-column-65ddf67e\"><div class=\"gb-container gb-container-65ddf67e\"><ul><li> <a href=\"https:\/\/exceladvisor.org\/nl\/lussen\/\">VBA-loops (beginner tot gevorderd)<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/selecteer-de-zaak\/\">VBA Selecteer geval<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/even-doen\/\">Excel VBA Do While Loop en (Do Loop While) &#8211; Een gids<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/lus-bladeren\/\">Loop door alle bladen met VBA in Excel<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/nl\/voor-de-lus\/\">VBA FOR LOOP (voor volgende, voor elke)<\/a> <\/li><\/ul><\/div><\/div><div class=\"gb-grid-column gb-grid-column-50067b57\"><div class=\"gb-container gb-container-50067b57\"><\/div><\/div><\/div><\/div>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"ub_ctt_via":"","footnotes":""},"class_list":["post-2146","page","type-page","status-publish","hentry"],"featured_image_src":null,"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Hoe navigeer je door een bereik met VBA? (Tutorial 2023)<\/title>\n<meta name=\"description\" content=\"Om een bereik in VBA te doorlopen, moet u de optie &quot;For Each Loop&quot; gebruiken. Met behulp van deze lus kun je code schrijven waarbij...\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/\" \/>\n<meta property=\"og:locale\" content=\"nl_NL\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Hoe navigeer je door een bereik met VBA? (Tutorial 2023)\" \/>\n<meta property=\"og:description\" content=\"Om een bereik in VBA te doorlopen, moet u de optie &quot;For Each Loop&quot; gebruiken. Met behulp van deze lus kun je code schrijven waarbij...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/\" \/>\n<meta property=\"og:site_name\" content=\"Excel Advisor\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-06T05:50:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-boucle-a-travers-la-gamme.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Geschatte leestijd\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minuten\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/\",\"url\":\"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/\",\"name\":\"Hoe navigeer je door een bereik met VBA? (Tutorial 2023)\",\"isPartOf\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-boucle-a-travers-la-gamme.png\",\"datePublished\":\"2023-09-28T22:43:00+00:00\",\"dateModified\":\"2023-12-06T05:50:29+00:00\",\"description\":\"Om een bereik in VBA te doorlopen, moet u de optie &quot;For Each Loop&quot; gebruiken. Met behulp van deze lus kun je code schrijven waarbij...\",\"breadcrumb\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/#breadcrumb\"},\"inLanguage\":\"nl-NL\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"nl-NL\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/#primaryimage\",\"url\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-boucle-a-travers-la-gamme.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-boucle-a-travers-la-gamme.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Thuis\",\"item\":\"https:\/\/exceladvisor.org\/nl\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Hoe navigeer je door een bereik met vba?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/#website\",\"url\":\"https:\/\/exceladvisor.org\/nl\/\",\"name\":\"Excel Advisor\",\"description\":\"Uw gids voor datadominantie\",\"publisher\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/exceladvisor.org\/nl\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"nl-NL\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/#organization\",\"name\":\"Excel Advisor\",\"url\":\"https:\/\/exceladvisor.org\/nl\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"nl-NL\",\"@id\":\"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"width\":105,\"height\":36,\"caption\":\"Excel Advisor\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Hoe navigeer je door een bereik met VBA? (Tutorial 2023)","description":"Om een bereik in VBA te doorlopen, moet u de optie &quot;For Each Loop&quot; gebruiken. Met behulp van deze lus kun je code schrijven waarbij...","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:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/","og_locale":"nl_NL","og_type":"article","og_title":"Hoe navigeer je door een bereik met VBA? (Tutorial 2023)","og_description":"Om een bereik in VBA te doorlopen, moet u de optie &quot;For Each Loop&quot; gebruiken. Met behulp van deze lus kun je code schrijven waarbij...","og_url":"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/","og_site_name":"Excel Advisor","article_modified_time":"2023-12-06T05:50:29+00:00","og_image":[{"url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-boucle-a-travers-la-gamme.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Geschatte leestijd":"2 minuten"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/","url":"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/","name":"Hoe navigeer je door een bereik met VBA? (Tutorial 2023)","isPartOf":{"@id":"https:\/\/exceladvisor.org\/nl\/#website"},"primaryImageOfPage":{"@id":"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/#primaryimage"},"image":{"@id":"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/#primaryimage"},"thumbnailUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-boucle-a-travers-la-gamme.png","datePublished":"2023-09-28T22:43:00+00:00","dateModified":"2023-12-06T05:50:29+00:00","description":"Om een bereik in VBA te doorlopen, moet u de optie &quot;For Each Loop&quot; gebruiken. Met behulp van deze lus kun je code schrijven waarbij...","breadcrumb":{"@id":"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/#breadcrumb"},"inLanguage":"nl-NL","potentialAction":[{"@type":"ReadAction","target":["https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/"]}]},{"@type":"ImageObject","inLanguage":"nl-NL","@id":"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/#primaryimage","url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-boucle-a-travers-la-gamme.png","contentUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-boucle-a-travers-la-gamme.png"},{"@type":"BreadcrumbList","@id":"https:\/\/exceladvisor.org\/nl\/loop-door-het-bereik\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Thuis","item":"https:\/\/exceladvisor.org\/nl\/"},{"@type":"ListItem","position":2,"name":"Hoe navigeer je door een bereik met vba?"}]},{"@type":"WebSite","@id":"https:\/\/exceladvisor.org\/nl\/#website","url":"https:\/\/exceladvisor.org\/nl\/","name":"Excel Advisor","description":"Uw gids voor datadominantie","publisher":{"@id":"https:\/\/exceladvisor.org\/nl\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/exceladvisor.org\/nl\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"nl-NL"},{"@type":"Organization","@id":"https:\/\/exceladvisor.org\/nl\/#organization","name":"Excel Advisor","url":"https:\/\/exceladvisor.org\/nl\/","logo":{"@type":"ImageObject","inLanguage":"nl-NL","@id":"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/","url":"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","contentUrl":"https:\/\/exceladvisor.org\/nl\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","width":105,"height":36,"caption":"Excel Advisor"},"image":{"@id":"https:\/\/exceladvisor.org\/nl\/#\/schema\/logo\/image\/"}}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages\/2146","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/comments?post=2146"}],"version-history":[{"count":1,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages\/2146\/revisions"}],"predecessor-version":[{"id":2736,"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/pages\/2146\/revisions\/2736"}],"wp:attachment":[{"href":"https:\/\/exceladvisor.org\/nl\/wp-json\/wp\/v2\/media?parent=2146"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}