{"id":307,"date":"2013-02-27T08:59:10","date_gmt":"2013-02-27T08:59:10","guid":{"rendered":"http:\/\/excelvbatutor.com\/?page_id=307"},"modified":"2020-04-23T10:53:45","modified_gmt":"2020-04-23T10:53:45","slug":"excel-vba-lesson-14-looping","status":"publish","type":"page","link":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-14-looping\/","title":{"rendered":"Excel VBA Lesson 14: Looping"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-13-select-case\/\">&lt;&lt;Lesson 13&gt;&gt;<\/a><a href=\"http:\/\/excelvbatutor.com\/index.php\/tutorial\/\"> [Contents] <\/a><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-15-do-loop\/\">&lt;&lt;Lesson 15&gt;&gt;<\/a><\/strong><\/h4>\n\n\n\n<p>Another procedure that involves decision making is looping.&nbsp;Excel VBA allows a procedure to be repeated many times until a condition or a set of conditions is fulfilled. This is generally called&nbsp;looping .&nbsp; Looping is a very useful feature of &nbsp;Excel VBA because it makes repetitive works easier. There are&nbsp; two kinds of loops in Visual Basic, &nbsp;the For&#8230;&#8230;.Next loop, the While&#8230;.Wend loop and the&nbsp;the Do&#8230;Loop . We shall deal with the For&#8230;&#8230;&#8230;Next loop &nbsp;and the&nbsp;While&#8230;.Wend loop&nbsp;first and we will deal with the Do&#8230;.Loop in the next lesson.<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>14.1 One level For&#8230;..Next Loop<\/strong><\/h3>\n\n\n\n<p>The one level For&#8230;.Next Loop event procedure is written as follows:<\/p>\n\n\n\n<p>For counter=startNumber to endNumber (Step increment)<\/p>\n\n\n\n<p>One or more VB statements<\/p>\n\n\n\n<p>Next<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example 14.1<\/strong><\/h4>\n\n\n\n<p>In this Excel VBA program, you place the command button 1 on the spreadsheet then click on it to go into the Visual Basic editor. When you click on the button, the Excel VBA program will fill cells(1,1) with the value of 1, cells(2,1) with the value of 2, cells(3,1) with the value of 3&#8230;&#8230;until cells (10,1) with the value of 10. The position of each cell in the Excel spreadsheet is referenced with cells(i,j), where i represents row and j represent the column.<\/p>\n\n\n\n<p><strong>The Code<\/strong><\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<br>\nDim i As Integer<br>\nFor i = 1 To 10<br>\nCells(i, 1).Value = i<br>\nNext<br>\nEnd Sub<\/p>\n\n\n\n<p>The Output is shown in Figure 5.1<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.vbtutor.net\/Images\/progra_jan_24_2008_vbaIV_1.gif\" alt=\"Excel VBA\"\/><\/figure>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br>\n<ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins><br>\n<script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><br>\n<strong>Figure 14.1<\/strong><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example 14.2<\/strong><\/h4>\n\n\n\n<p>For&#8230;&#8230;..Next loop with step increments. In this example, the number increases by 2 at a time. The resulting series is 1,3,5,7,9 displayed in Cells(1,1), Cells(3,1),Cells(5,1), Cells(7,1) and Cells(9,1) respectively.<\/p>\n\n\n\n<p><strong>The code<\/strong><\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<br>\nDim i As Integer<br>\nFor i = 1 To 10 &nbsp;Step 2<br>\nCells(i, 1).Value = i<br>\nNext<br>\nEnd Sub<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><a href=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/02\/vba_example5.2.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"347\" height=\"360\" src=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/02\/vba_example5.2.jpg\" alt=\"Excel VBA\" class=\"wp-image-318\"\/><\/a><\/figure><\/div>\n\n\n\n<p><strong>The Output<\/strong><br>\n\n<\/p>\n\n\n\n<p>&nbsp;<strong>Figure 14.2<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">&nbsp;<strong>14.2 Nested For&#8230;.Next Loops<\/strong><\/h3>\n\n\n\n<p>Nested For&#8230;&#8230;.Next loops mean there are more than one For&#8230;Next Loops are nested within the first level For&#8230;..Next loop. The structure is as follows:<\/p>\n\n\n\n<p>For counter=<em>startNumber1<\/em> to <em>endNumber1<\/em><br>\nFor counter=<em>startNumber2<\/em> to <em>endNumber2<\/em><br>\nFor counter=<em>startNumber3<\/em> to <em>endNumber3<\/em><br>\nOne or more VB statements<br>\nNext<br>\nNext<br>\nNext<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example 14.3<\/strong><\/h4>\n\n\n\n<p>In this example , we use &nbsp;nested loop to put the values of i+j from cells(1,1),cells(1,2),cells(1,3),cells(1,4),cells(1,5) &#8230;&#8230;&#8230;.until cells(10,5). The code and output are shown below.<\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<br>\nDim i, j As Integer<br>\nFor i = 1 To 10<br>\nFor j = 1 To 5<br>\nCells(i, j).Value = i + j<br>\nNext<br>\nNext<br>\nEnd Sub<\/p>\n\n\n\n<p><strong>The Output<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><figure><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" src=\"https:\/\/www.vbtutor.net\/Images\/progra_jan_24_2008_vbaIV_2.gif\" alt=\"Excel VBA\" width=\"452\" height=\"372\"><\/figure>\n<p style=\"text-align: center;\"><strong>Figure 14.3<\/strong><\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>14.3 &nbsp;While&#8230;.Wend &nbsp;Loop<\/strong><\/h3>\n\n\n\n<p>The structure of a While\u2026.Wend Loop is very similar to the Do Loop. it takes the following form:<\/p>\n\n\n\n<p>While condition<br>\nStatements<br>\nWend<\/p>\n\n\n\n<p>The above loop means that while the condition is not met, the loop will go on. The loop will end when the condition is met. Let\u2019s examine the program listed in example 14.4.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example 14.4<\/strong><\/h4>\n\n\n\n<p>In this example, the Excel VBA program will compute the square of numbers from 1 to 15 and displays them from Cells(1,1) to Cells(15,1)<\/p>\n\n\n\n<p><strong>The Code<\/strong><\/p>\n\n\n\n<p>Private Sub CommandButton1_Click()<br>\nDim i As Integer<\/p>\n\n\n\n<p>i = 1<br>\nWhile i &lt;= 15<\/p>\n\n\n\n<p>Cells(i, 1) = i ^ 2<br>\ni = i + 1<br>\nWend<\/p>\n\n\n\n<p>End Sub<\/p>\n\n\n\n<p><strong>The Output<\/strong><\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><a href=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/02\/vba_example5.4.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"285\" height=\"430\" src=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/02\/vba_example5.4.jpg\" alt=\"vba_example5.4\" class=\"wp-image-321\"\/><\/a><\/figure><\/div>\n\n\n\n<p><strong>Figure 14.4<\/strong><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-13-select-case\/\">&lt;&lt;Lesson 13&gt;&gt;<\/a><a href=\"http:\/\/excelvbatutor.com\/index.php\/tutorial\/\"> [Contents] <\/a><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-15-do-loop\/\">&lt;&lt;Lesson 15&gt;&gt;<\/a><\/strong><\/h4>\n","protected":false},"excerpt":{"rendered":"<p>&lt;&lt;Lesson 13&gt;&gt; [Contents] &lt;&lt;Lesson 15&gt;&gt; Another procedure that involves decision making is looping.&nbsp;Excel VBA allows a procedure to be repeated many times until a condition or a set of conditions is fulfilled. This is generally called&nbsp;looping .&nbsp; Looping is a very useful feature of &nbsp;Excel VBA because it makes repetitive works easier. There are&nbsp; two &hellip; <a href=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-14-looping\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Excel VBA Lesson 14: Looping&#8221;<\/span><\/a><\/p>\n","protected":false},"author":5012,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[5,11],"tags":[],"class_list":["post-307","page","type-page","status-publish","hentry","category-decison-making","category-loop"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Excel VBA Lesson 14: Looping - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor<\/title>\n<meta name=\"description\" content=\"This Excel VBA lesson demonstrates the usage of looping in Excel VBA\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/excelvbatutor.com\/vba_lesson14.htm\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel VBA Lesson 14: Looping - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"og:description\" content=\"This Excel VBA lesson demonstrates the usage of looping in Excel VBA\" \/>\n<meta property=\"og:url\" content=\"https:\/\/excelvbatutor.com\/vba_lesson14.htm\" \/>\n<meta property=\"og:site_name\" content=\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-23T10:53:45+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.vbtutor.net\/Images\/progra_jan_24_2008_vbaIV_1.gif\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-14-looping\/\",\"url\":\"https:\/\/excelvbatutor.com\/vba_lesson14.htm\",\"name\":\"Excel VBA Lesson 14: Looping - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"isPartOf\":{\"@id\":\"https:\/\/excelvbatutor.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson14.htm#primaryimage\"},\"image\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson14.htm#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.vbtutor.net\/Images\/progra_jan_24_2008_vbaIV_1.gif\",\"datePublished\":\"2013-02-27T08:59:10+00:00\",\"dateModified\":\"2020-04-23T10:53:45+00:00\",\"description\":\"This Excel VBA lesson demonstrates the usage of looping in Excel VBA\",\"breadcrumb\":{\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson14.htm#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/excelvbatutor.com\/vba_lesson14.htm\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson14.htm#primaryimage\",\"url\":\"https:\/\/www.vbtutor.net\/Images\/progra_jan_24_2008_vbaIV_1.gif\",\"contentUrl\":\"https:\/\/www.vbtutor.net\/Images\/progra_jan_24_2008_vbaIV_1.gif\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/excelvbatutor.com\/vba_lesson14.htm#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/excelvbatutor.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel VBA Lesson 14: Looping\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/excelvbatutor.com\/#website\",\"url\":\"https:\/\/excelvbatutor.com\/\",\"name\":\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"description\":\"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.\",\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel VBA Lesson 14: Looping - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"This Excel VBA lesson demonstrates the usage of looping in Excel VBA","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:\/\/excelvbatutor.com\/vba_lesson14.htm","og_locale":"en_US","og_type":"article","og_title":"Excel VBA Lesson 14: Looping - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","og_description":"This Excel VBA lesson demonstrates the usage of looping in Excel VBA","og_url":"https:\/\/excelvbatutor.com\/vba_lesson14.htm","og_site_name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","article_modified_time":"2020-04-23T10:53:45+00:00","og_image":[{"url":"https:\/\/www.vbtutor.net\/Images\/progra_jan_24_2008_vbaIV_1.gif","type":"","width":"","height":""}],"twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-14-looping\/","url":"https:\/\/excelvbatutor.com\/vba_lesson14.htm","name":"Excel VBA Lesson 14: Looping - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","isPartOf":{"@id":"https:\/\/excelvbatutor.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson14.htm#primaryimage"},"image":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson14.htm#primaryimage"},"thumbnailUrl":"https:\/\/www.vbtutor.net\/Images\/progra_jan_24_2008_vbaIV_1.gif","datePublished":"2013-02-27T08:59:10+00:00","dateModified":"2020-04-23T10:53:45+00:00","description":"This Excel VBA lesson demonstrates the usage of looping in Excel VBA","breadcrumb":{"@id":"https:\/\/excelvbatutor.com\/vba_lesson14.htm#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/excelvbatutor.com\/vba_lesson14.htm"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/excelvbatutor.com\/vba_lesson14.htm#primaryimage","url":"https:\/\/www.vbtutor.net\/Images\/progra_jan_24_2008_vbaIV_1.gif","contentUrl":"https:\/\/www.vbtutor.net\/Images\/progra_jan_24_2008_vbaIV_1.gif"},{"@type":"BreadcrumbList","@id":"https:\/\/excelvbatutor.com\/vba_lesson14.htm#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/excelvbatutor.com\/"},{"@type":"ListItem","position":2,"name":"Excel VBA Lesson 14: Looping"}]},{"@type":"WebSite","@id":"https:\/\/excelvbatutor.com\/#website","url":"https:\/\/excelvbatutor.com\/","name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.","inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/307","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/users\/5012"}],"replies":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/comments?post=307"}],"version-history":[{"count":31,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/307\/revisions"}],"predecessor-version":[{"id":3459,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/307\/revisions\/3459"}],"wp:attachment":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/media?parent=307"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/categories?post=307"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/tags?post=307"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}