Multiple cell selection in VBA using ActiveCell.Offset(0, 1).Value

Question

I'm starting in VBA and had to do a program that is able to retrieve value from a selected cell which will be used as reference.

I'm able to do it when I select one cell with the Activecell function and playing around with the ActiveCell.Offset(0, 1).Value etc. However how to make the same thing while selecting multiple cells at the same time and be able to take the 1 cell value at a time and do Activecell.Offset... then identify the second cell value and retrieve the proper information and so on.

Using macro recorder I see that when I select multiple values it point to

Range("Y8,Y9,Y10,Y11").Select 'etc....

Thank you for your help and hope I've been precise enough on what I'm trying to do.

Many thanks olivier

Answer 1

Either use

Range(ActiveCell, ActiveCell.Offset(0, 1))

or

ActiveCell.Resize(1, 2)

Then you can use it that way

For Each cell In Range(ActiveCell, ActiveCell.Offset(0, 1))
    Debug.Print cell.Value
Next cell

which is equivalent to

Range(ActiveCell, ActiveCell.Offset(0, 1)).Select ' or ActiveCell.Resize(1, 2).Select
For i = 0 To 1
    Debug.Print ActiveCell.Offset(0, i)
Next i

Answer 2

I know this is kinda late and the OP probably has the solution but I think what he wants can be achieved by using Selection like:

Dim r As Range, c As Range

If TypeOf Selection Is Range Then Set r = Selection Else Exit Sub

For Each c In r
    '/* put the code you want here */
    Debug.Print c.Address
    DebUg.Print c.Offset(0,1).Value
Next

Posting as answer just in case someone stumbled on the same problem/issue/requirement.

Answer 3

I was just trying to figure this out today and came across this post, so I thought I would update it with a link to where I found a simple answer.

I go this from https://excelchamps.com/vba/range-offset/

Range(Range("A1").Offset(1, 1), Range("A1").Offset(5, 2)).Select

So using the OP's desire to use ActiveCell as the starting location and an arbitrary range to select using an Offset, it would look something like this:

Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(5, 2)).Select