{"id":374,"date":"2019-01-19T23:17:54","date_gmt":"2019-01-19T23:17:54","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=374"},"modified":"2019-01-29T04:15:08","modified_gmt":"2019-01-29T04:15:08","slug":"what-is-for-each-loop-in-vba","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2019\/01\/19\/what-is-for-each-loop-in-vba\/","title":{"rendered":"What is for each loop in VBA?"},"content":{"rendered":"

What is for each loop in VBA?<\/p>\n

A for each loop in VBA is a way of interating
\nthrough a range of cells in your worksheet.<\/p>\n

For instance, below is my data, I need to place in a column. <\/p>\n

\"\"<\/a><\/p>\n

Here I am adding a button called “Put In Column”<\/p>\n

\"\"<\/a><\/p>\n

You will need to assign the “ForEachLoop” macro to the new button.<\/p>\n

Then I just select the cells on row “A”, I want to put into a column and click my button! <\/p>\n

Now all my row data is in the column (look at the image):<\/p>\n

\"\"<\/a><\/p>\n

Here is the code:<\/p>\n

Sub ForEachLoop()\r\n    Dim rngSelection As Range\r\n    Dim rngCell As Range\r\n    \r\n    Dim intRow As Integer\r\n    \r\n    Set rngSelection = Selection.Cells 'Whatever I selected is the Selection\r\n    \r\n    'start at row\r\n    intRow = 3\r\n    \r\n    For Each rngCell In rngSelection\r\n        Range(\"A\" & intRow) = rngCell.Value\r\n        intRow = intRow + 1\r\n    Next\r\n    \r\n    \r\nEnd Sub\r\n<\/pre>\n
I know you can use the Copy command and choose the Transpose option of Paste Special,
\nbut this is an example, and I was actually using this to speed the process of generating the create
\ntable SQL syntax on CSV files I was receiving.<\/p>\n
Watch me do it:<\/p>\n