Wrong range selected using .range.cells

[cbrf23]

Not sure whats going on here... but with the code below, if I type:

?rngwhole.Address

in the immediate window I get:

$A$20:$M$20 '[[correct answer]]

Then if I type:

?rngwhole.Range(.Cells(1, 1), .Cells(1, 2)).Address

I get:

$A$39:$B$39 '[[correct column, BUT +19 rows off!]]

Anyone have any idea whats going on?

Code in question:

Sub testy()
    Dim rngWhole As Range
 
    Set rngWhole = Range("A20:M20")
 
    With rngWhole
            With .Range(.Cells(1, 1), .Cells(1, 2))
                .Select
                .HorizontalAlignment = xlCenterAcrossSelection
                .VerticalAlignment = xlBottom
                .WrapText = True
            End With
            With .Cells(1, 3)
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlBottom
                .WrapText = True
            End With
            With .Range(.Cells(1, 10), .Cells(1, 12))
                .Select
                .HorizontalAlignment = xlCenterAcrossSelection
                .VerticalAlignment = xlBottom
                .WrapText = True
            End With
     End With
End Sub

 

[AlphaFrog]

?rngwhole.Range([COLOR="Red"].[/COLOR]Cells(1, 1), [COLOR="Red"].[/COLOR]Cells(1, 2)).Address

The .Cells are using the With statement as well. It is equivalent to...

?rngwhole.Range([COLOR="Red"]Range("A20:M20")[/COLOR].Cells(1, 1), [COLOR="Red"]Range("A20:M20")[/COLOR].Cells(1, 2)).Address

I think you want this...

?rngwhole.Range(Cells(1, 1), Cells(1, 2)).Address

 

[Jonmo1]

OK, let's break it down.

If rngWhole = A20:M20
Then
rngWhole.Cells(1,1) = A20
rngWhole.Cells(1,2) = B20

With me so far

So that translates to rngWhole.Range(A20:B20)
The A20:B20 is RELATIVE to the original address of rngWhole (A20:M20)
So it's basically 20 rows from A20 (including A20) = A39


This is why most of us dislike doing Range(...).Range(...)
It gets very confusing.


I think what you want is
rngWhole.Resize(1,2)


Hope that helps.

 

[cbrf23]

AlphaFrog - genius! thank you.

JonMo1 - I am not super familiar with the resize method...I'll have to check it out on the msdn and see what I can find out about it. Thank you for the suggestion.

 

[mikerickson]

Remove the dot from the two argument Range

Sub testy()
    Dim rngWhole As Range
 
    Set rngWhole = Range("A20:M20")
 
    With rngWhole
            With Range(.Cells(1, 1), .Cells(1, 2)): Rem <<<<<<<
                .Select
                .HorizontalAlignment = xlCenterAcrossSelection
                .VerticalAlignment = xlBottom
                .WrapText = True
            End With
            With .Cells(1, 3)
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlBottom
                .WrapText = True
            End With
            With Range(.Cells(1, 10), .Cells(1, 12)): Rem <<<<<<<
                .Select
                .HorizontalAlignment = xlCenterAcrossSelection
                .VerticalAlignment = xlBottom
                .WrapText = True
            End With
     End With
End Sub