Loop over excel cells using for loop when cells are selected using CTRL

Question

I am trying to loop over cells which are not selected in sequence, rather they are selected using mouse and CTRL.

When I use for each loop, then it works, but when I try for next then the result is not as expected.

The purpose of asking this question is to understand the difference between both the methods and to find if I am doing something wrong in the loops.

Approach 1: For Each loop

Sub foreachloop()
Dim oRange As Range
Set oRange = Selection
For Each cell In oRange
cell.Offset(0, -1).Value = 2
Next cell
End Sub

Result: As expected (See screenshot)

Approach 2: For Next loop

Sub fornextloop()
Dim oRange As Range
Set oRange = Selection
For i = 1 To oRange.Count
oRange.Cells(i).Offset(0, -1).Value = 1
Next i
End Sub

Result: Actual (See screenshot)

For a use case, I need to use For Next loop and I still want to achieve the expected output.

Answer 1

Cells or ranges that were selected via Ctrl+Click consist of multiple Areas. To use the For Next approach, loop through each Cell within each Area:

Option Explicit

Sub ForNextLoop()
    Dim oRange As Range, i As Long, j As Long
    Set oRange = Application.Selection
    For i = 1 To oRange.Areas.Count
        For j = 1 To oRange.Areas(i).Cells.Count
            oRange.Areas(i).Cells(j).Offset(0, -1).Value = 1
        Next j
    Next i
End Sub

I also recommend that you explicitly declare all of your variables. Using Option Explicit at the top of your module will force you to do so. In the case of the For Each approach, cell (or item) should be declared As Range:

Sub ForEachLoop()
    Dim oRange As Range, Item As Range
    Set oRange = Application.Selection
    For Each Item In oRange
        Item.Offset(0, -1).Value = 2
    Next Item
End Sub