Error resulting from coding

  • Thread starter Thread starter Jim May
  • Start date Start date
J

Jim May

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)
 
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!!
 
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
 
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
 
Tom: Thanks for pointing out
As is only used in a declaration/DIM statement.
Click to expand...
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
 
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)
 
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
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code to delete names works in one WB, but not another WB? 2
Problem with VBA Functions that use a DefinedRange as parameter 6
Run-Time Error 1004 on ActiveChart.SeriesCollection(1).Values = 2
Resize Table Range to exclude zero values and Input New Range into achart object 10
problem:Structure with Object reference (TYPE...END TYPE) 6
Range error 3
OLE Link bug introduced in Excel 2003 SP3 1
Change the value of particular cells in a Named Range 7

Back
Top