? typename(Range("A1:B9").Value)
Variant()

You do what you want.

--
Regards,
Tom Ogilvy

"Bob Kilmer" wrote in message
...
"Tom Ogilvy" wrote in message
...
Value is the default property of a range.
Then in the For loop, you'd be meaning "for each value in the range"
but
you use r as if it were a Range object that has a Value.

Moreover, the control variable r needs to be a Variant or an Object. A
Double won't do.

This 'moreover" comment was misconceived. I was thinking it supported a
"value is not default property of a range" argument, but it does not.


What code are you looking at

Range("C2:C" & Range("A65535").End(xlUp).Row)
is a multicell range

r is dimmed as Range and the control variable should and will be a range

So far so good.

Cells(row, column) - not sure how that would be the default property
of
a
range even if the two arguments can be left off.


Help says Item is the default property of a range, which returns a Range,
but this is an untidy digression.

The suggestion by an earlier poster that I supported was to qualify the
Range by appending the Cells property. I say its merit is to make the
statement Range("C2:C" & Range("A65535").End(xlUp).Row) unambiguous. "I
mean
the cells in this range, not the columns or the rows which are also part
of
this range." Not being explicit leaves it up to the reader/maintenance
programmer to interpret which of the alternatives is meant and lets VBA
decide which meaning applies.

you couldn't do sStr = r unless value was the default.

What is the value of the range Range("A1:B2")? Can you assign this value
to
any non-variant?

If Range("C2:C" & Range("A65535").End(xlUp).Row) is a multicell range and
Value is the default property of a range, why does r end up containing a
range, and a single cell range at that, rather than a value?

However, in the
code in question r .Value is specified - there is no implicit or default
assignment.

The code in question for me is Range("C2:C" &
Range("A65535").End(xlUp).Row). The question is "Is it a good idea to
qualify it with Cells or not?"

In your little demo program. As you see in the first two cases. there
is
no
default value invoked because in this context a range is a range which
is
what we are looping through.

I am suggesting that unless you ask for something else, you get a single
cell range, which makes a single cell range the default under the
circumstances. One could ask for Columns or Rows or Values.

And as you demonstrate, adding cells
accomplishes nothing. (my whole original comment in this thread)

... except that it says to everyone that, "I mean the cells in this range
and not rows or columns!" which is my original point. Truely, this is a
pretty trivial example, but make it a little more complicated. Suppose it
were a larger, mulit-dimensional range in a significant piece of code and
you meant to code Range(blahblah).Columns but unintentionally left off
Columns. The code might well run, but some maintenance programmer down the
road trying to figure out what the code was doing - or was meant to have
been doing - absent copious comments - might not find it obvious whether
you
meant Cells, Rows, or Columns.

In your
third loop, you are looping through an array, so whatever the content of
the array is will be what type returns.

Array? I am explicit requesting Values from the iterator of the
collection.

Just one more time.
in the constuct

for each r in Range("C2:C" & Range("A65535").End(xlUp).Row)

Range("C2:C" & Range("A65535").End(xlUp).Row) is interpreted as a range
object - there is no default value - it is a range that is needed and a

range that is provided.

Or, a collection of cells (not columns or rows) is needed and the default
behavior of a multi-cell range in this context is that of a collection of
cells and we may as well be explicit about it.


--
Regards,
Tom Ogilvy


Truely, regards to you as well, Tom.
Bob



"Bob Kilmer" wrote in message
...
Value is the default property of a range.
Then in the For loop, you'd be meaning "for each value in the range"
but
you use r as if it were a Range object that has a Value.

For Each r in Range("C2:C" & Range("A65535").End(xlUp).Row)
If r.Value = strFind Then ...

Moreover, the control variable r needs to be a Variant or an Object. A
Double won't do.

Adding Cells doesn't make any difference because the expression
returns
a single cell Range into r anyway, which the code uses as such.

I find that under Cells Property in Excel help it is says "the Item
property is the default property for the Range object" which, in the
case of a Range object, "returns a Range object". Clearly, Range(x)
behaves different ways under different circumstances as VBA guesses at
what is meant by what is written. I'd say at best, using the Range
property without a qualifier is context dependent. Reasonably
dependable, but context dependent, and therefore ambiguous.

Range("B1:B10") = 5 'assigns 5 as the Value of each cell
? Range("B1:B10") 'doesn't work at all - type mismatch
? Typename(Range("B1:B10")) 'returns Range

Sub Main()
Dim r
'Prints "Range"
For Each r In Range("A1:A10")
Debug.Print CStr(TypeName(r))
Next r
'Prints "Range"
For Each r In Range("A1:A10").Cells
Debug.Print CStr(TypeName(r))
Next r
'Prints "Double," or "String," or ...
For Each r In Range("A1:A10").Value
Debug.Print CStr(TypeName(r))
Next r
End Sub

I try to be explicit and would tend to use Cells under the present
circumstances because I believe that is what is being returned into r
and my intent would be to use r as such.

Regards,
Bob


Tom Ogilvy wrote:
Value is the default property of a range. Not sure where returning
a
range
object has anything to do with default values. In the usage shown,
Cells
would have no effect.

The place where it would be needed is if Columns was appended to end
of
the
statement:

? Range("C2:C" & Range("A65535").End(xlUp).Row).Count
2
? Range("C2:C" & Range("A65535").End(xlUp).Row).columns.count
1
? Range("C2:C" & Range("A65535").End(xlUp).Row).Columns.Cells.Count
2

But appending cells to the original has no affect:

? Range("C2:C" & Range("A65535").End(xlUp).Row).cells.Count
2