PDA

View Full Version : Solved: Range.Cells.Range.Cells ... grrrrr



alimcpill
09-15-2004, 02:47 AM
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
09-15-2004, 05:53 AM
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
09-15-2004, 06:08 AM
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
09-16-2004, 05:15 AM
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!