changing merged cells from code

[john]

I have a spreadsheet that is protected and mostly locked. I do not have a
password. There is a merged range, I2:H2, where a user can manually input
data. When I record a macro I get code like this:

Range("H2:I2").Select
ActiveCell.FormulaR1C1 = "456"

or

Range("H2:I2").Select
Selection.ClearContents

When I attempt to include this code in my VBA macro I get "Select method
of Range class failed".

So far nothing else I've tried works either. I can neither read nor write
to the merged area from code. Is there anything I can do?

John

P.S. Anyone know how can I search all newsgroup message text using Opera?
The quickfind only appears to search the message title.

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

[Dave Peterson]

Does

Range("H2").value = 456
and
range("h2").value = ""

work ok?

(Are those cells unlocked? Format|cells|Protection tab to check.)

And I'd visit google to search the archives of the newsgroups.
http://groups.google.com/advanced_group_search

john wrote:
>
> I have a spreadsheet that is protected and mostly locked. I do not have a
> password. There is a merged range, I2:H2, where a user can manually input
> data. When I record a macro I get code like this:
>
> Range("H2:I2").Select
> ActiveCell.FormulaR1C1 = "456"
>
> or
>
> Range("H2:I2").Select
> Selection.ClearContents
>
> When I attempt to include this code in my VBA macro I get "Select method
> of Range class failed".
>
> So far nothing else I've tried works either. I can neither read nor write
> to the merged area from code. Is there anything I can do?
>
> John
>
> P.S. Anyone know how can I search all newsgroup message text using Opera?
> The quickfind only appears to search the message title.
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

--

Dave Peterson

[john]

Thanks. By going back to check all the expressions that did and did not
work I found that I was writing to the wrong workbook.

The macro was called by a command button on Sheet1 of Book1. The macro
opens Book2, selects mySheet in Book2, and changes Range("H2").value.

Despite the ActivewWorbook being Book2, and the ActiveSheet being mySheet,
Range("H2").value still referred to Sheet1 of Book 1.

Using Activesheet.Range("H2").Value in my assignment statement fixed my
problem, even though something is still flakey here.


John



On Tue, 05 Jul 2005 18:08:23 -0700, Dave Peterson
<[email protected]> wrote:

> Does
>
> Range("H2").value = 456
> and
> range("h2").value = ""
>
> work ok?
>
> (Are those cells unlocked? Format|cells|Protection tab to check.)
>
> And I'd visit google to search the archives of the newsgroups.
> http://groups.google.com/advanced_group_search
>
> john wrote:
>>
>> I have a spreadsheet that is protected and mostly locked. I do not
>> have a
>> password. There is a merged range, I2:H2, where a user can manually
>> input
>> data. When I record a macro I get code like this:
>>
>> Range("H2:I2").Select
>> ActiveCell.FormulaR1C1 = "456"
>>
>> or
>>
>> Range("H2:I2").Select
>> Selection.ClearContents
>>
>> When I attempt to include this code in my VBA macro I get "Select method
>> of Range class failed".
>>
>> So far nothing else I've tried works either. I can neither read nor
>> write
>> to the merged area from code. Is there anything I can do?
>>
>> John
>>
>> P.S. Anyone know how can I search all newsgroup message text using
>> Opera?
>> The quickfind only appears to search the message title.
>>
>> --
>> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

[Dave Peterson]

It sounds like your code may be under a worksheet module--instead of a general
module.

If you have an unqualified range in a general module, it'll refer to the
activesheet.

If you have an unqualified range in a worksheet module, it'll refer to the
worksheet that owns the code.

So this:

worksheets("sheet2").select
range("a1").select

will work in a general module, but will fail in the module for a worksheet named
"Sheet1".


john wrote:
>
> Thanks. By going back to check all the expressions that did and did not
> work I found that I was writing to the wrong workbook.
>
> The macro was called by a command button on Sheet1 of Book1. The macro
> opens Book2, selects mySheet in Book2, and changes Range("H2").value.
>
> Despite the ActivewWorbook being Book2, and the ActiveSheet being mySheet,
> Range("H2").value still referred to Sheet1 of Book 1.
>
> Using Activesheet.Range("H2").Value in my assignment statement fixed my
> problem, even though something is still flakey here.
>
> John
>
> On Tue, 05 Jul 2005 18:08:23 -0700, Dave Peterson
> <[email protected]> wrote:
>
> > Does
> >
> > Range("H2").value = 456
> > and
> > range("h2").value = ""
> >
> > work ok?
> >
> > (Are those cells unlocked? Format|cells|Protection tab to check.)
> >
> > And I'd visit google to search the archives of the newsgroups.
> > http://groups.google.com/advanced_group_search
> >
> > john wrote:
> >>
> >> I have a spreadsheet that is protected and mostly locked. I do not
> >> have a
> >> password. There is a merged range, I2:H2, where a user can manually
> >> input
> >> data. When I record a macro I get code like this:
> >>
> >> Range("H2:I2").Select
> >> ActiveCell.FormulaR1C1 = "456"
> >>
> >> or
> >>
> >> Range("H2:I2").Select
> >> Selection.ClearContents
> >>
> >> When I attempt to include this code in my VBA macro I get "Select method
> >> of Range class failed".
> >>
> >> So far nothing else I've tried works either. I can neither read nor
> >> write
> >> to the merged area from code. Is there anything I can do?
> >>
> >> John
> >>
> >> P.S. Anyone know how can I search all newsgroup message text using
> >> Opera?
> >> The quickfind only appears to search the message title.
> >>
> >> --
> >> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
> >
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

--

Dave Peterson

[john]

Thank you for the explanation.
John

On Wed, 06 Jul 2005 05:53:40 -0700, Dave Peterson
<[email protected]> wrote:

> It sounds like your code may be under a worksheet module--instead of a
> general
> module.
>
> If you have an unqualified range in a general module, it'll refer to the
> activesheet.
>
> If you have an unqualified range in a worksheet module, it'll refer to
> the
> worksheet that owns the code.
>
> So this:
>
> worksheets("sheet2").select
> range("a1").select
>
> will work in a general module, but will fail in the module for a
> worksheet named
> "Sheet1".
>
>
> john wrote:
>>
>> Thanks. By going back to check all the expressions that did and did not
>> work I found that I was writing to the wrong workbook.
>>
>> The macro was called by a command button on Sheet1 of Book1. The macro
>> opens Book2, selects mySheet in Book2, and changes Range("H2").value.
>>
>> Despite the ActivewWorbook being Book2, and the ActiveSheet being
>> mySheet,
>> Range("H2").value still referred to Sheet1 of Book 1.
>>
>> Using Activesheet.Range("H2").Value in my assignment statement fixed my
>> problem, even though something is still flakey here.
>>
>> John
>>
>> On Tue, 05 Jul 2005 18:08:23 -0700, Dave Peterson
>> <[email protected]> wrote:
>>
>> > Does
>> >
>> > Range("H2").value = 456
>> > and
>> > range("h2").value = ""
>> >
>> > work ok?
>> >
>> > (Are those cells unlocked? Format|cells|Protection tab to check.)
>> >
>> > And I'd visit google to search the archives of the newsgroups.
>> > http://groups.google.com/advanced_group_search
>> >
>> > john wrote:
>> >>
>> >> I have a spreadsheet that is protected and mostly locked. I do not
>> >> have a
>> >> password. There is a merged range, I2:H2, where a user can manually
>> >> input
>> >> data. When I record a macro I get code like this:
>> >>
>> >> Range("H2:I2").Select
>> >> ActiveCell.FormulaR1C1 = "456"
>> >>
>> >> or
>> >>
>> >> Range("H2:I2").Select
>> >> Selection.ClearContents
>> >>
>> >> When I attempt to include this code in my VBA macro I get "Select
>> method
>> >> of Range class failed".
>> >>
>> >> So far nothing else I've tried works either. I can neither read nor
>> >> write
>> >> to the merged area from code. Is there anything I can do?
>> >>
>> >> John
>> >>
>> >> P.S. Anyone know how can I search all newsgroup message text using
>> >> Opera?
>> >> The quickfind only appears to search the message title.
>> >>
>> >> --
>> >> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
>> >
>>
>> --
>> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

[Dave Peterson]

Yeah, but was it close to accurate?

john wrote:
>
> Thank you for the explanation.
> John
>
> On Wed, 06 Jul 2005 05:53:40 -0700, Dave Peterson
> <[email protected]> wrote:
>
> > It sounds like your code may be under a worksheet module--instead of a
> > general
> > module.
> >
> > If you have an unqualified range in a general module, it'll refer to the
> > activesheet.
> >
> > If you have an unqualified range in a worksheet module, it'll refer to
> > the
> > worksheet that owns the code.
> >
> > So this:
> >
> > worksheets("sheet2").select
> > range("a1").select
> >
> > will work in a general module, but will fail in the module for a
> > worksheet named
> > "Sheet1".
> >
> >
> > john wrote:
> >>
> >> Thanks. By going back to check all the expressions that did and did not
> >> work I found that I was writing to the wrong workbook.
> >>
> >> The macro was called by a command button on Sheet1 of Book1. The macro
> >> opens Book2, selects mySheet in Book2, and changes Range("H2").value.
> >>
> >> Despite the ActivewWorbook being Book2, and the ActiveSheet being
> >> mySheet,
> >> Range("H2").value still referred to Sheet1 of Book 1.
> >>
> >> Using Activesheet.Range("H2").Value in my assignment statement fixed my
> >> problem, even though something is still flakey here.
> >>
> >> John
> >>
> >> On Tue, 05 Jul 2005 18:08:23 -0700, Dave Peterson
> >> <[email protected]> wrote:
> >>
> >> > Does
> >> >
> >> > Range("H2").value = 456
> >> > and
> >> > range("h2").value = ""
> >> >
> >> > work ok?
> >> >
> >> > (Are those cells unlocked? Format|cells|Protection tab to check.)
> >> >
> >> > And I'd visit google to search the archives of the newsgroups.
> >> > http://groups.google.com/advanced_group_search
> >> >
> >> > john wrote:
> >> >>
> >> >> I have a spreadsheet that is protected and mostly locked. I do not
> >> >> have a
> >> >> password. There is a merged range, I2:H2, where a user can manually
> >> >> input
> >> >> data. When I record a macro I get code like this:
> >> >>
> >> >> Range("H2:I2").Select
> >> >> ActiveCell.FormulaR1C1 = "456"
> >> >>
> >> >> or
> >> >>
> >> >> Range("H2:I2").Select
> >> >> Selection.ClearContents
> >> >>
> >> >> When I attempt to include this code in my VBA macro I get "Select
> >> method
> >> >> of Range class failed".
> >> >>
> >> >> So far nothing else I've tried works either. I can neither read nor
> >> >> write
> >> >> to the merged area from code. Is there anything I can do?
> >> >>
> >> >> John
> >> >>
> >> >> P.S. Anyone know how can I search all newsgroup message text using
> >> >> Opera?
> >> >> The quickfind only appears to search the message title.
> >> >>
> >> >> --
> >> >> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
> >> >
> >>
> >> --
> >> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
> >
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

--

Dave Peterson