Simple for each loop problem

[L0lax89]

Hello everyone,
This problem I have is really bugging me.
I'm a beginner to VBA and want to write a code which does things to the cells in the first column from a user selection. This is the code if the selection is contiguous:


Sub simpleloop1()
Dim v_currang As Range
Dim v_selrang As Range

Set v_selrang = Selection.Columns(1)

For Each v_currang In v_selrang
'code goes here, next line as example
v_currang.Select
Next v_currang
End Sub


This doesn't work. When reaching the v_currang.Select part, it selects the entire Selection.Columns(1) range and exits the loop.
The next example works, it does loop through the entire range:


Sub simpleloop2()
Dim v_currang As Range
Dim v_selrang As Range

Selection.Columns(1).Select
Set v_selrang = Selection

For Each v_currang In v_selrang
'code goes here, next line as example
v_currang.Select
Next v_currang
End Sub


Why does the second work but the first doesn't?

Thx

[Leith Ross]

Hello L0lax89,

In VBA it is rarely necessary to select and object before performing an action. The macro recorder does this because it is the easiest method of constructing VBA code automatically. However, it is not a pattern you as a programmer should follow. There are cleaner, faster, and more efficient methods of coding.

The Range Object has a few types of Objects: Rows, Columns, and Cells. Even though the Row and Column extend across the worksheet horizontally and vertically, it is treated as a single Range Object. To access the cells with the row or column, you must use the Cells method of the Range object to return the collection of cells within the given row or column.

Your first loop does address the cells within the column of the selection. It is a single Column object. Applying the Cells method will make it work correctly.


Sub simpleloop1()
Dim v_currang As Range
Dim v_selrang As Range

Set v_selrang = Selection.Columns(1)

For Each v_currang In v_selrang.Cells
'code goes here, next line as example
v_currang.Select
Next v_currang
End Sub


The reason the second loop works is you have selected the cells in the selection...


Selection.Columns(1).Select
Set v_selrang = Selection



Here is an example of writing a sequential number in to each cell of the first column in the user's selection.


Sub simpleloop()


Dim n As Long
Dim v_currang As Range
Dim v_selrang As Range

Set v_selrang = Selection.Columns(1)

For Each v_currang In v_selrang.Cells
'code goes here, next line as example
n = n + 1
v_currang.Value = n
Next v_currang

End Sub

[L0lax89]

Ok, I get it now. Adding .Cells to Selection.Columns(1) fixed the problem. I've noticed that setting a range variable to = Selection also works, and loops through all cells in the selected range. Selection defaults to Selection.Cells from what I understand.
Thank you for the advice!

[Leith Ross]

Hello L0lax89,

I'm impressed. This is often a difficult concept for beginners to understand.
You're welcome.