Range.Cells.Range.Cells

[alimcpill]

Good morning experts. After receiving such useful help for my last query I hope I can gain some help for this one as well. This one is driving me mental, I just cannot for the life of me work out what Excel is doing:

Basically I am specifying ranges within ranges. However, the following behaviour baffles me. Maybe my computer is playing up!! Just put this code in a standard module in a new workbook, and put a couple of break points in to see what it does.

Sub DoIt()
    Dim xls As Excel.Worksheet
    Set xls = ActiveSheet
With xls.Range(xls.Cells(5, 5), xls.Cells(15, 15))
        ' The following selects E5:O15, as I expected
        .Select
'The next line selects I9:K11. Why???
        'I expected it to select E5:G7!!!
        .Range(.Cells(1, 1), .Cells(3, 3)).Select
    End With
End Sub

I use ".Cells(1,1), .Cells(3,3)" for the second range because I want the cells relative to the range specified by the With statement.

If I have the second range statement as

.Range("A1:C3").Select

Then it selects the cells I want, but I really want to access the cells with numbers rather than letters as in my actual code there is iteration going on, which clearly is easier to do with numbers rather than letters.

please help!!

[mdmackillop]

Try

Sub DoIt()
Dim xls As Excel.Worksheet
    Set xls = ActiveSheet
With xls.Range(xls.Cells(5, 5), xls.Cells(15, 15))
         ' The following selects E5:O15, as I expected
        .Select
    End With
With Selection
        .Range(Cells(1, 1), Cells(3, 3)).Select
    End With
End Sub

[TonyJollans]

This is not entirely intuitive but what is happening is that by using .Range and .Cells you are applying two offsets.

Either do as already suggested by MD or, my preference, ..

Range(.Cells(1,1),.Cells(3,3))

[alimcpill]

aha now i get it, I am kicking myself for not trying it out myself, wouldn't have been hard to test it a couple of times removing the odd dot or two! Thanks mdmackillop and tonyjollans, you have once again restored my sanity!