[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Breaking up long line of code doesn't work

Hector Fernandez

12/17/2006 2:20:00 PM

I am trying to perform a single event to multiple ranges but for some
reason I can't get the macro to work if I write it on multiple lines.

If, for example, I write:

Sub Clear_Ranges()

Range("range1,range2,range3") = clear

End Sub

The macro works just fine and clears the ranges. However, if I do the
following:

Sub Clear_Ranges()

Range("range1, _
range2, _
range3") = clear

End Sub

I get an error message "Compile Error: Expected: List separator or )"

I thought that using a space and underscore was suppose to allow you to
write a single line of code across several lines in order to make it
easier to read or fit in a page.

I intend to have a lot more than three ranges in the macro and it will
be very messy and long if I try to put it on a single line.

What am I doing wrong?
Is there an easier way to perform the same thing on several different
ranges?

Thanks.
13 Answers

Gary''s Student

12/17/2006 2:39:00 PM

0

Try UNION()


Sub lots_of_ranges()
Dim range1 As Range, range2 As Range, range3 As Range, r As Range
Set range1 = Range("A1:A10")
Set range2 = Range("B4:b56")
Set range3 = Range("Z100:Z103")

Set r = Union(range1, range2, range3)
r.Clear
End Sub




--
Gary's Student


"Hector Fernandez" wrote:

> I am trying to perform a single event to multiple ranges but for some
> reason I can't get the macro to work if I write it on multiple lines.
>
> If, for example, I write:
>
> Sub Clear_Ranges()
>
> Range("range1,range2,range3") = clear
>
> End Sub
>
> The macro works just fine and clears the ranges. However, if I do the
> following:
>
> Sub Clear_Ranges()
>
> Range("range1, _
> range2, _
> range3") = clear
>
> End Sub
>
> I get an error message "Compile Error: Expected: List separator or )"
>
> I thought that using a space and underscore was suppose to allow you to
> write a single line of code across several lines in order to make it
> easier to read or fit in a page.
>
> I intend to have a lot more than three ranges in the macro and it will
> be very messy and long if I try to put it on a single line.
>
> What am I doing wrong?
> Is there an easier way to perform the same thing on several different
> ranges?
>
> Thanks.
>
>

Don Guillett

12/17/2006 2:42:00 PM

0

Usually but it appears, in this case, no. So, just make several lines..
Range("range1,range2,range3") = clear
Range("range4,range5,range6") = clear
Of course, if contiguos, not necessary.

range("a2, c2:f2 ,l2")
--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Hector Fernandez" <public.research@gmail.com> wrote in message
news:1166365184.592178.236300@j72g2000cwa.googlegroups.com...
>I am trying to perform a single event to multiple ranges but for some
> reason I can't get the macro to work if I write it on multiple lines.
>
> If, for example, I write:
>
> Sub Clear_Ranges()
>
> Range("range1,range2,range3") = clear
>
> End Sub
>
> The macro works just fine and clears the ranges. However, if I do the
> following:
>
> Sub Clear_Ranges()
>
> Range("range1, _
> range2, _
> range3") = clear
>
> End Sub
>
> I get an error message "Compile Error: Expected: List separator or )"
>
> I thought that using a space and underscore was suppose to allow you to
> write a single line of code across several lines in order to make it
> easier to read or fit in a page.
>
> I intend to have a lot more than three ranges in the macro and it will
> be very messy and long if I try to put it on a single line.
>
> What am I doing wrong?
> Is there an easier way to perform the same thing on several different
> ranges?
>
> Thanks.
>


Hector Fernandez

12/17/2006 2:52:00 PM

0

Gary, thanks for the response - unfortunately your method is more work
than simply writing

Range("range1") = clear
Range("range2") = clear
....and so forth

Is there some reason why the approach you suggest would be better?

Don, unless someone comes up with an alternative, I may have to resort
to your suggestion.

Thanks.

Mike Fogleman

12/17/2006 3:06:00 PM

0

You should not use line continuation within a parentheses. You should use
Don's suggestion.

Mike F
"Hector Fernandez" <public.research@gmail.com> wrote in message
news:1166367116.398302.92410@n67g2000cwd.googlegroups.com...
> Gary, thanks for the response - unfortunately your method is more work
> than simply writing
>
> Range("range1") = clear
> Range("range2") = clear
> ...and so forth
>
> Is there some reason why the approach you suggest would be better?
>
> Don, unless someone comes up with an alternative, I may have to resort
> to your suggestion.
>
> Thanks.
>


Jim Cone

12/17/2006 3:12:00 PM

0

Hector,
That is a text string you are breaking up.
You need quote marks around each segment.
Using just line breaks won't work.

"This is a very long text string" can be split into three lines this way...
"This is a very " & _
"long " & _
"text string"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primiti...


"Hector Fernandez"
<public.research@gmail.com>
wrote in message
I am trying to perform a single event to multiple ranges but for some
reason I can't get the macro to work if I write it on multiple lines.

If, for example, I write:
Sub Clear_Ranges()
Range("range1,range2,range3") = clear
End Sub

The macro works just fine and clears the ranges. However, if I do the
following:
Sub Clear_Ranges()
Range("range1, _
range2, _
range3") = clear
End Sub

I get an error message "Compile Error: Expected: List separator or )"
I thought that using a space and underscore was suppose to allow you to
write a single line of code across several lines in order to make it
easier to read or fit in a page.
I intend to have a lot more than three ranges in the macro and it will
be very messy and long if I try to put it on a single line.
What am I doing wrong?
Is there an easier way to perform the same thing on several different
ranges?
Thanks.

Don Guillett

12/17/2006 3:27:00 PM

0

Did you try that for this case?

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
news:OSaSq2eIHHA.1912@TK2MSFTNGP03.phx.gbl...
> Hector,
> That is a text string you are breaking up.
> You need quote marks around each segment.
> Using just line breaks won't work.
>
> "This is a very long text string" can be split into three lines this
> way...
> "This is a very " & _
> "long " & _
> "text string"
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primiti...
>
>
> "Hector Fernandez"
> <public.research@gmail.com>
> wrote in message
> I am trying to perform a single event to multiple ranges but for some
> reason I can't get the macro to work if I write it on multiple lines.
>
> If, for example, I write:
> Sub Clear_Ranges()
> Range("range1,range2,range3") = clear
> End Sub
>
> The macro works just fine and clears the ranges. However, if I do the
> following:
> Sub Clear_Ranges()
> Range("range1, _
> range2, _
> range3") = clear
> End Sub
>
> I get an error message "Compile Error: Expected: List separator or )"
> I thought that using a space and underscore was suppose to allow you to
> write a single line of code across several lines in order to make it
> easier to read or fit in a page.
> I intend to have a lot more than three ranges in the macro and it will
> be very messy and long if I try to put it on a single line.
> What am I doing wrong?
> Is there an easier way to perform the same thing on several different
> ranges?
> Thanks.
>


Jim Cone

12/17/2006 4:04:00 PM

0


Don,
'rng1, rng2, rng3 are named ranges... A1, B2, C3

Sub AAA()
Range("rng1," & _
"rng2," & _
"rng3").Value = "Hector"
End Sub

Sub BBB()
Range("rng1," & _
"rng2," & _
"rng3").Clear
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primiti...



"Don Guillett" <dguillett1@austin.rr.com>
wrote in message
Did you try that for this case?
--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com


"Jim Cone"
<jim.coneXXX@rcn.comXXX>
wrote in message
> Hector,
> That is a text string you are breaking up.
> You need quote marks around each segment.
> Using just line breaks won't work.
>
> "This is a very long text string" can be split into three lines this
> way...
> "This is a very " & _
> "long " & _
> "text string"
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primiti...
>
>
> "Hector Fernandez"
> <public.research@gmail.com>
> wrote in message
> I am trying to perform a single event to multiple ranges but for some
> reason I can't get the macro to work if I write it on multiple lines.
>
> If, for example, I write:
> Sub Clear_Ranges()
> Range("range1,range2,range3") = clear
> End Sub
>
> The macro works just fine and clears the ranges. However, if I do the
> following:
> Sub Clear_Ranges()
> Range("range1, _
> range2, _
> range3") = clear
> End Sub
>
> I get an error message "Compile Error: Expected: List separator or )"
> I thought that using a space and underscore was suppose to allow you to
> write a single line of code across several lines in order to make it
> easier to read or fit in a page.
> I intend to have a lot more than three ranges in the macro and it will
> be very messy and long if I try to put it on a single line.
> What am I doing wrong?
> Is there an easier way to perform the same thing on several different
> ranges?
> Thanks.
>


Gary Keramidas

12/17/2006 5:16:00 PM

0

i've use something like this before, seemed to work fine. using the same method
you show here

Range("this_is_range1,this_is_range2," & _
"this_is_range3,this_is_range4,this_is_range5, " & _
"this_is_range6,this_is_range7").Value = "hector"

--


Gary


"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
news:%23SSpxTfIHHA.1064@TK2MSFTNGP04.phx.gbl...
>
> Don,
> 'rng1, rng2, rng3 are named ranges... A1, B2, C3
>
> Sub AAA()
> Range("rng1," & _
> "rng2," & _
> "rng3").Value = "Hector"
> End Sub
>
> Sub BBB()
> Range("rng1," & _
> "rng2," & _
> "rng3").Clear
> End Sub
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primiti...
>
>
>
> "Don Guillett" <dguillett1@austin.rr.com>
> wrote in message
> Did you try that for this case?
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
>
>
> "Jim Cone"
> <jim.coneXXX@rcn.comXXX>
> wrote in message
>> Hector,
>> That is a text string you are breaking up.
>> You need quote marks around each segment.
>> Using just line breaks won't work.
>>
>> "This is a very long text string" can be split into three lines this
>> way...
>> "This is a very " & _
>> "long " & _
>> "text string"
>> --
>> Jim Cone
>> San Francisco, USA
>> http://www.realezsites.com/bus/primiti...
>>
>>
>> "Hector Fernandez"
>> <public.research@gmail.com>
>> wrote in message
>> I am trying to perform a single event to multiple ranges but for some
>> reason I can't get the macro to work if I write it on multiple lines.
>>
>> If, for example, I write:
>> Sub Clear_Ranges()
>> Range("range1,range2,range3") = clear
>> End Sub
>>
>> The macro works just fine and clears the ranges. However, if I do the
>> following:
>> Sub Clear_Ranges()
>> Range("range1, _
>> range2, _
>> range3") = clear
>> End Sub
>>
>> I get an error message "Compile Error: Expected: List separator or )"
>> I thought that using a space and underscore was suppose to allow you to
>> write a single line of code across several lines in order to make it
>> easier to read or fit in a page.
>> I intend to have a lot more than three ranges in the macro and it will
>> be very messy and long if I try to put it on a single line.
>> What am I doing wrong?
>> Is there an easier way to perform the same thing on several different
>> ranges?
>> Thanks.
>>
>
>


Hector Fernandez

12/18/2006 12:46:00 AM

0


Don, I live in Spain so it's late now, but will try Jim and Gary's
recommendations tomorrow morning (my time) and let you guys know how it
went.

Thanks to all of you.

H?ctor

Hector Fernandez

12/18/2006 11:49:00 AM

0

Don, yes, it works great.

Thanks to all.

Héctor