In a new wb and ws:
In the Immediate Window I enter:
range("A1:A10").Name = "TestRange"
? Range("TestRange").address
$A$1:$A$10 <<< results of above line
Range("TestRange").Resize(5,3) As Range << When I enter this I get

Compile error:

Expected: End of Statement

Why?

Becuase you are not declaring a variable.

I assume that you want to redefine the name, so try

Range("TestRange").Resize(5,3).Name="TestRange"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:ND%Ad.15517$jn.4876@lakeread06...
In a new wb and ws:
In the Immediate Window I enter:
range("A1:A10").Name = "TestRange"
? Range("TestRange").address
$A$1:$A$10 <<< results of above line
Range("TestRange").Resize(5,3) As Range << When I enter this I get

Compile error:

Expected: End of Statement

Why?

Ok, while waiting I consulted my Excel 2002 VBA Prog Ref and see
that the following is required, still uncertain why, but
Range("TestRange").Resize(5,3).Name = "TestRange" ' This works!!

"Jim May" wrote in message
news:ND%Ad.15517$jn.4876@lakeread06...
In a new wb and ws:
In the Immediate Window I enter:
range("A1:A10").Name = "TestRange"
? Range("TestRange").address
$A$1:$A$10 <<< results of above line
Range("TestRange").Resize(5,3) As Range << When I enter this I get

Compile error:

Expected: End of Statement

Why?

As is only used in a declaration/DIM statement.

set rng = Range("TestRange").Resize(5,3)

is possibly what you want.


or perhaps

Range("TestRange").Resize(5,3).Name = "TestRange"

to redefine TestRange
--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:ND%Ad.15517$jn.4876@lakeread06...
In a new wb and ws:
In the Immediate Window I enter:
range("A1:A10").Name = "TestRange"
? Range("TestRange").address
$A$1:$A$10 <<< results of above line
Range("TestRange").Resize(5,3) As Range << When I enter this I get

Compile error:

Expected: End of Statement

Why?

an additional (important) lesson for me which I need to know... Question?
Any time one sees the comment:
Expected: End of Statement
....loosely translated - does this mean "you have stopped too soon (with
what's needed)?"
as was the case with me in failing to "tack-on" .name = "TestRange"
Thanks again,
Jim

"Bob Phillips" wrote in message
...
Becuase you are not declaring a variable.

I assume that you want to redefine the name, so try

Range("TestRange").Resize(5,3).Name="TestRange"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:ND%Ad.15517$jn.4876@lakeread06...
In a new wb and ws:
In the Immediate Window I enter:
range("A1:A10").Name = "TestRange"
? Range("TestRange").address
$A$1:$A$10 <<< results of above line
Range("TestRange").Resize(5,3) As Range << When I enter this I get

Compile error:

Expected: End of Statement

Why?

Tom: Thanks for pointing out
As is only used in a declaration/DIM statement.
Initially I didn't have the "As Range" part.. only
added it thinking that was what was needed..
I failed to remove from my post, but glad now I left it
in as I didn't know the above rule you just gave me; and this rule is good
to know.
Tks,
Jim

"Tom Ogilvy" wrote in message
...
As is only used in a declaration/DIM statement.

set rng = Range("TestRange").Resize(5,3)

is possibly what you want.


or perhaps

Range("TestRange").Resize(5,3).Name = "TestRange"

to redefine TestRange
--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:ND%Ad.15517$jn.4876@lakeread06...
In a new wb and ws:
In the Immediate Window I enter:
range("A1:A10").Name = "TestRange"
? Range("TestRange").address
$A$1:$A$10 <<< results of above line
Range("TestRange").Resize(5,3) As Range << When I enter this I get

Compile error:

Expected: End of Statement

Why?

Jim,

What it means is that the parser has come to the end and it hasn't been able
to fully resolve the statement, it expects something else. Often happens
with parentheses, but in this case it needed to be told to do something to
the range defined, but it wasn't happy with what it got.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:VM0Bd.15530$jn.310@lakeread06...
an additional (important) lesson for me which I need to know... Question?
Any time one sees the comment:
Expected: End of Statement
...loosely translated - does this mean "you have stopped too soon (with
what's needed)?"
as was the case with me in failing to "tack-on" .name = "TestRange"
Thanks again,
Jim

"Bob Phillips" wrote in message
...
Becuase you are not declaring a variable.

I assume that you want to redefine the name, so try

Range("TestRange").Resize(5,3).Name="TestRange"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:ND%Ad.15517$jn.4876@lakeread06...
In a new wb and ws:
In the Immediate Window I enter:
range("A1:A10").Name = "TestRange"
? Range("TestRange").address
$A$1:$A$10 <<< results of above line
Range("TestRange").Resize(5,3) As Range << When I enter this I get

Compile error:

Expected: End of Statement

Why?

Bob:
Thanks for the note. Appreciate you and so many others who give of your
time and experience with Excel. Hope you and yours have a Happy New Year.
Jim May

"Bob Phillips" wrote in message
...
Jim,

What it means is that the parser has come to the end and it hasn't been
able
to fully resolve the statement, it expects something else. Often happens
with parentheses, but in this case it needed to be told to do something to
the range defined, but it wasn't happy with what it got.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:VM0Bd.15530$jn.310@lakeread06...
an additional (important) lesson for me which I need to know...
Question?
Any time one sees the comment:
Expected: End of Statement
...loosely translated - does this mean "you have stopped too soon (with
what's needed)?"
as was the case with me in failing to "tack-on" .name = "TestRange"
Thanks again,
Jim

"Bob Phillips" wrote in message
...
Becuase you are not declaring a variable.

I assume that you want to redefine the name, so try

Range("TestRange").Resize(5,3).Name="TestRange"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:ND%Ad.15517$jn.4876@lakeread06...
In a new wb and ws:
In the Immediate Window I enter:
range("A1:A10").Name = "TestRange"
? Range("TestRange").address
$A$1:$A$10 <<< results of above line
Range("TestRange").Resize(5,3) As Range << When I enter this I
get

Compile error:

Expected: End of Statement

Why?

Thanks Jim, really appreciate such kind comments. All the best to you and
your family for the New Year, and to all of our Excel colleagues.

Bob


"Jim May" wrote in message
news:NxhBd.16025$jn.8922@lakeread06...
Bob:
Thanks for the note. Appreciate you and so many others who give of your
time and experience with Excel. Hope you and yours have a Happy New Year.
Jim May

"Bob Phillips" wrote in message
...
Jim,

What it means is that the parser has come to the end and it hasn't been
able
to fully resolve the statement, it expects something else. Often happens
with parentheses, but in this case it needed to be told to do something
to
the range defined, but it wasn't happy with what it got.

--

HTH

RP
(remove nothere from the email address if mailing direct)