{"id":139,"date":"2015-10-24T12:18:32","date_gmt":"2015-10-24T12:18:32","guid":{"rendered":"http:\/\/99excel.com\/?p=139"},"modified":"2021-05-14T16:09:21","modified_gmt":"2021-05-14T16:09:21","slug":"for-each-loop","status":"publish","type":"post","link":"https:\/\/99excel.com\/home\/for-each-loop\/","title":{"rendered":"For Each loop"},"content":{"rendered":"

For Each<\/strong><\/p>\n

This Loop is slightly different from the others, but only in the fact that it requires an Object as the Variable. What it does is simply Loop though each single Object in a Collection of Objects<\/strong>.<\/p>\n

 <\/p>\n

Sub My_ForEach()<\/p>\n

Dim rMyCell As Range<\/p>\n

Dim iMyNumber As Integer<\/p>\n

For Each<\/strong> rMyCell In <\/strong>Range(“A1:A100”)<\/p>\n

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

rMyCell.Value = iMyNumber<\/p>\n

Next <\/strong>rMyCell<\/p>\n

End Sub<\/p>\n

 <\/p>\n

For Each Cell in the Range A1 to Range A100 add 1 to the Variable iMyNumber2 Where “Cell” is represented by the Range Variable “rMyCell” So it will do this 100 times as there are 100 Range Objects in the Object Collection Range(“A1:A100”)<\/p>\n

 <\/p>\n

We do not need to tell the For Each<\/strong> Loop how many times to Loop as it already knows how many Objects (Cells in this case) there are in the Object Collection (Range(A1:A100)<\/strong>).<\/p>\n

 <\/p>\n

Our Object Collection does not have to be a Range Collection, it could be a Charts, Worksheets, Workbooks etc Collection. In fact it can be any Collection of Objects. So if we wanted to Loop through all Worksheets in a Workbook we could use:<\/p>\n

 <\/p>\n

Sub My_ForEach1()<\/p>\n

Dim wWsht As Worksheet<\/p>\n

For Each<\/strong> wWsht In<\/strong> ThisWorkbook.Worksheets<\/p>\n

wWsht.Range(“A1”) = wWsht.Name<\/p>\n

\u00a0\u00a0\u00a0 Next<\/strong> wWsht<\/p>\n

End Sub<\/p>\n","protected":false},"excerpt":{"rendered":"

For Each This Loop is slightly different from the others, but only in the fact that it requires an Object as the Variable. What it does is simply Loop though each single Object in a Collection of Objects.   Sub My_ForEach() Dim rMyCell As Range Dim iMyNumber As Integer For Each rMyCell In Range(“A1:A100”) iMyNumber … 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-139","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\/139","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=139"}],"version-history":[{"count":1,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/139\/revisions"}],"predecessor-version":[{"id":887,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/139\/revisions\/887"}],"wp:attachment":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/media?parent=139"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/categories?post=139"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/tags?post=139"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}