{"id":129,"date":"2015-10-24T12:16:10","date_gmt":"2015-10-24T12:16:10","guid":{"rendered":"http:\/\/99excel.com\/?p=129"},"modified":"2021-05-14T16:09:21","modified_gmt":"2021-05-14T16:09:21","slug":"use-of-loops","status":"publish","type":"post","link":"https:\/\/99excel.com\/home\/use-of-loops\/","title":{"rendered":"Use of Loops"},"content":{"rendered":"

L<\/strong>ooping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.<\/p>\n

 <\/p>\n

Let’s look at each one in turn and use them in a simple way.<\/p>\n

\u00a0<\/u><\/p>\n

Do Loop<\/strong><\/p>\n

 <\/p>\n

START:<\/strong> \u00a0Do <\/strong>(tell our Loop)<\/p>\n

STOP:<\/strong> Do While <\/strong>(This is very similar to our Do Loop we just used above. The only difference is, instead of the condition we set (iMyNumber < 1000) being checked AFTER the Do has run at least once, the Do While will check the condition BEFORE it runs. Let’s say our Variable iMyNumber has already had the Value 1001 parsed to it. In the Do Loop we used above, it will NOT know the Value of iMyNumber until it has run ONCE. This would mean our Variable would come out of the Do Loop with a Value of 1002. But in the Do While below, it would never even enter the Loop)<\/p>\n

 <\/p>\n

Sub My_Do()<\/p>\n

Do<\/strong><\/p>\n

<Code to repeat><\/em><\/p>\n

Loop While<\/strong> <Whatever><\/em><\/p>\n

End Sub<\/p>\n

 <\/p>\n

Progress:<\/strong> Add the number one to a Integer Variable we called\u00a0 “iMyNumber”<\/p>\n

 <\/p>\n

 <\/p>\n

Sub My_Do1 ()<\/p>\n

Dim iMyNumber as Integer<\/p>\n

Do<\/strong><\/p>\n

iMyNumber=1+iMyNumber<\/p>\n

Loop While<\/strong> <Whatever><\/p>\n

End Sub<\/p>\n

 <\/p>\n

Progress:<\/strong> Ok, so we have now told our “Do”<\/strong> what it is we want to do. Now we have to tell it when to stop. To achieve this we must set a condition for our “Loop While”<\/strong>. In this case we will: Do iMyNumber=1+iMyNumber<\/strong> until iMyNumber is > 1000<\/strong>.<\/p>\n

 <\/p>\n

 <\/p>\n

Sub My_Do2 ()<\/p>\n

Dim iMyNumber as Integer<\/p>\n

Do<\/strong><\/p>\n

iMyNumber = 1 + iMyNumber<\/p>\n

Loop While<\/strong> iMyNumber < 1000<\/p>\n

Range(“b2”).Value = iMyNumber<\/p>\n

End Sub<\/p>\n

 <\/p>\n

So the bottom line here is the Do Loop will Loop 1001 times<\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"

Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.   Let’s look at each one in turn and use them in a simple way. \u00a0 Do Loop   START: \u00a0Do (tell our Loop) STOP: Do … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-129","post","type-post","status-publish","format-standard","hentry"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/129","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/comments?post=129"}],"version-history":[{"count":1,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/129\/revisions"}],"predecessor-version":[{"id":892,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/129\/revisions\/892"}],"wp:attachment":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/media?parent=129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/categories?post=129"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/tags?post=129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}