Am I Range? ... I object!!!
 

Look... I need some help understanding something. I know with your wisdom,
you will be able to get this through my thick skull.

What really is the difference between thse declarations:

Dim Var as Range
Dim Var as Object

When I think of a range... I think of a range object. The key word being
"object".

The reason I bring up this confusion, mainly, is that if I set a range
variable to a range, that " RangeValue", doesn't carry with it all the
properties of where it came from.

Condider this: sheet1 is active

Set MyRange = Range("A2").CurrentRegion

MyRange is no a "range" object which points to the curren region connected
to cell A1 on Sheet1

What if I want to set MyRange to a region on another sheet??? The following
is not valid code:

Set MyRange = sheets("Sheet3").Range("A1").CurrentRegion

Is this where an "Object" variable come into play?

I realize that I can use:

MyRange = MyOtherRange.address(external:=True)

to get the full reference, but this does return a string variable... not a
range

Can someone Plleeease help me to understand this.

John

Am I Range? ... I object!!!
 

? activesheet.name
Sheet1
set myrange = sheets("sheet2").Range("A1").CurrentRegion
? myrange.Address(external:=true)
[Book1]Sheet2!$A$1:$E$18

Your assertion that

Set MyRange = sheets("Sheet3").Range("A1").CurrentRegion

is not valid code is incorrect.

If you want to use a range, dim the variable as Range

Object is more generic.

If I want to work with the sheets collection

for each sh in ActiveWorkbook.Sheets

then I would

Dim sh as Object

because sh can hold different objects. Worksheets, MacroSheets, charts,
etc

A range is a range. The parent of a range object is a worksheet. Object
isn't a special form of range. It is just a much more general declaration.

--
Regards,
Tom Ogilvy



John T Ingato wrote in message
...
Look... I need some help understanding something. I know with your
wisdom,
you will be able to get this through my thick skull.

What really is the difference between thse declarations:

Dim Var as Range
Dim Var as Object

When I think of a range... I think of a range object. The key word being
"object".

The reason I bring up this confusion, mainly, is that if I set a range
variable to a range, that " RangeValue", doesn't carry with it all the
properties of where it came from.

Condider this: sheet1 is active

Set MyRange = Range("A2").CurrentRegion

MyRange is no a "range" object which points to the curren region connected
to cell A1 on Sheet1

What if I want to set MyRange to a region on another sheet??? The
following
is not valid code:

Set MyRange = sheets("Sheet3").Range("A1").CurrentRegion

Is this where an "Object" variable come into play?

I realize that I can use:

MyRange = MyOtherRange.address(external:=True)

to get the full reference, but this does return a string variable... not a
range

Can someone Plleeease help me to understand this.

John

Am I Range? ... I object!!!
 

Referring to your main query <<What really is the difference between
thse declarations perhaps the following macro will help. (Copy/paste
into a module).

If you define a variable as Object then the Set statement can be used
to assign it to any type of object. This might be useful in a long
running macro where it is used often because it reserves memory space
at the beginning of your program where the system will find it faster.
Having it further down makes it slower to find.

'------------------------------------------------
Sub test()
Dim Var As Object
'---------------------------------
'- Var as Range
Set Var = ActiveSheet.Range("A1")
'- Var.Value works here
MsgBox ("Range A1 " & Var.Value)
'---------------------------------
'- reset Var as Worksheet
Set Var = ActiveSheet
'- correct usage of new Var
MsgBox ("Sheet.Range A1 " & Var.Range("A1").Value)
'- the following line crashes the macro because a
'- Worksheet does not have a .Value property
x = Var.Value
'---------------------------
End Sub
'--------------------------------------------------



"John T Ingato" wrote in message ...
Look... I need some help understanding something. I know with your wisdom,
you will be able to get this through my thick skull.

What really is the difference between thse declarations:

Dim Var as Range
Dim Var as Object

When I think of a range... I think of a range object. The key word being
"object".

The reason I bring up this confusion, mainly, is that if I set a range
variable to a range, that " RangeValue", doesn't carry with it all the
properties of where it came from.

Condider this: sheet1 is active

Set MyRange = Range("A2").CurrentRegion

MyRange is no a "range" object which points to the curren region connected
to cell A1 on Sheet1

What if I want to set MyRange to a region on another sheet??? The following
is not valid code:

Set MyRange = sheets("Sheet3").Range("A1").CurrentRegion

Is this where an "Object" variable come into play?

I realize that I can use:

MyRange = MyOtherRange.address(external:=True)

to get the full reference, but this does return a string variable... not a
range

Can someone Plleeease help me to understand this.

John

Am I Range? ... I object!!!
 

"John T Ingato" wrote in message
<snip

You definitely need to leave the .address off, as that returns a string
rather than a range.

Your problem is that the code is trying to select a cell on a sheet that is
not activated.

Solution is to insert
Sheets("Stores").activate

Put this in before
Set rStoreRange = FindEndOfDataIn("Col", rCurrentRecord,"range")



To help see the basics, I produced a simplified version of your code, which
all seems to work perfectly - see below

HTH

GB



Public Sub Testit()

Dim MyRange As Range
Set MyRange = Sheets("Sheet3").Range("A1")

Sheets("Sheet3").Activate 'change this to Sheet1 and it will generate your
particular error
Range("E1").Select

Set rStoreRange = FindEndOfDataIn("Col", MyRange, "Range")

Debug.Print rStoreRange.Address

End Sub


Public Function FindEndOfDataIn(Selection As String, StartCell As Range,
Optional ReturnType As String) As Variant

StartCell.Select

Range("F2").Activate

Set FindEndOfDataIn = Range(StartCell, ActiveCell)


End Function