I am using a commandbutton_click sub on a specific worksheet to automate some
reset features on a timesheet I've built. I want to be able to clear and
reset values on other worksheets in the same workbook at the same time.
Inside the commandbutton1_click sub on "Sheet1" I use the code

Worksheet("Sheet2").Range(cells(rowcounter,
columncounter),cells(rowcounter+2,columncounter +2).clearcontents

where rowcounter and columncounter are locally dimmed integers with verified
values (I can see them in the debug)

However, I get a run time 1004 error when this line executes -- if I change
the range designation above to "A1:C3" format, I don't get the error. Is it
a problem that rowcounter and columncounter are locally defined?

Any help or advice would be appreciated.

Thanks.

I'd bet it's because the cells() stuff isn't qualified:

with Worksheet("Sheet2")
.Range(.cells(rowcounter, columncounter), _
.cells(rowcounter+2,columncounter +2)).clearcontents
end with

ps. it's better to copy|paste from the VBE than to retype the code and
introduce other typos. (Like missing a closing paren.)



Frank wrote:
>
> I am using a commandbutton_click sub on a specific worksheet to automate some
> reset features on a timesheet I've built. I want to be able to clear and
> reset values on other worksheets in the same workbook at the same time.
> Inside the commandbutton1_click sub on "Sheet1" I use the code
>
> Worksheet("Sheet2").Range(cells(rowcounter,
> columncounter),cells(rowcounter+2,columncounter +2).clearcontents
>
> where rowcounter and columncounter are locally dimmed integers with verified
> values (I can see them in the debug)
>
> However, I get a run time 1004 error when this line executes -- if I change
> the range designation above to "A1:C3" format, I don't get the error. Is it
> a problem that rowcounter and columncounter are locally defined?
>
> Any help or advice would be appreciated.
>
> Thanks.

--

Dave Peterson

) misssing. I've done this too

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Frank" <(E-Mail Removed)> wrote in message
news:AD8283DA-0F07-4E5B-901D-(E-Mail Removed)...
>I am using a commandbutton_click sub on a specific worksheet to automate
>some
> reset features on a timesheet I've built. I want to be able to clear and
> reset values on other worksheets in the same workbook at the same time.
> Inside the commandbutton1_click sub on "Sheet1" I use the code
>
> Worksheet("Sheet2").Range(cells(rowcounter,
> columncounter),cells(rowcounter+2,columncounter +2).clearcontents
>
> where rowcounter and columncounter are locally dimmed integers with
> verified
> values (I can see them in the debug)
>
> However, I get a run time 1004 error when this line executes -- if I
> change
> the range designation above to "A1:C3" format, I don't get the error. Is
> it
> a problem that rowcounter and columncounter are locally defined?
>
> Any help or advice would be appreciated.
>
> Thanks.

OK, wow, it worked PERFECTLY!!! Magic!! Thanks a ton!!!!!!!

Weird -- how isn't the cells stuff qualifed in the original? It leads off
with the Worksheets designation for the target worksheet?

"Dave Peterson" wrote:

> I'd bet it's because the cells() stuff isn't qualified:
>
> with Worksheet("Sheet2")
> .Range(.cells(rowcounter, columncounter), _
> .cells(rowcounter+2,columncounter +2)).clearcontents
> end with
>
> ps. it's better to copy|paste from the VBE than to retype the code and
> introduce other typos. (Like missing a closing paren.)
>
>
>
> Frank wrote:
> >
> > I am using a commandbutton_click sub on a specific worksheet to automate some
> > reset features on a timesheet I've built. I want to be able to clear and
> > reset values on other worksheets in the same workbook at the same time.
> > Inside the commandbutton1_click sub on "Sheet1" I use the code
> >
> > Worksheet("Sheet2").Range(cells(rowcounter,
> > columncounter),cells(rowcounter+2,columncounter +2).clearcontents
> >
> > where rowcounter and columncounter are locally dimmed integers with verified
> > values (I can see them in the debug)
> >
> > However, I get a run time 1004 error when this line executes -- if I change
> > the range designation above to "A1:C3" format, I don't get the error. Is it
> > a problem that rowcounter and columncounter are locally defined?
> >
> > Any help or advice would be appreciated.
> >
> > Thanks.
>
> --
>
> Dave Peterson
>

In your original .range was qualified, but cells weren't:

Worksheet("Sheet2").Range(cells(rowcounter, columncounter), _
cells(rowcounter+2,columncounter +2).clearcontents

You could use:
Worksheet("Sheet2").Range(Worksheet("Sheet2").cells(rowcounter,columncounter), _
Worksheet("Sheet2").cells(rowcounter+2,columncounter +2)).clearcontents

If you were a glutton for punishment, er, typing.

Those unqualified cells() will refer to either the activesheet (in a General
module) or the sheet that owns the code (if the code is in a worksheet module).





Frank wrote:
>
> OK, wow, it worked PERFECTLY!!! Magic!! Thanks a ton!!!!!!!
>
> Weird -- how isn't the cells stuff qualifed in the original? It leads off
> with the Worksheets designation for the target worksheet?
>
> "Dave Peterson" wrote:
>
> > I'd bet it's because the cells() stuff isn't qualified:
> >
> > with Worksheet("Sheet2")
> > .Range(.cells(rowcounter, columncounter), _
> > .cells(rowcounter+2,columncounter +2)).clearcontents
> > end with
> >
> > ps. it's better to copy|paste from the VBE than to retype the code and
> > introduce other typos. (Like missing a closing paren.)
> >
> >
> >
> > Frank wrote:
> > >
> > > I am using a commandbutton_click sub on a specific worksheet to automate some
> > > reset features on a timesheet I've built. I want to be able to clear and
> > > reset values on other worksheets in the same workbook at the same time.
> > > Inside the commandbutton1_click sub on "Sheet1" I use the code
> > >
> > > Worksheet("Sheet2").Range(cells(rowcounter,
> > > columncounter),cells(rowcounter+2,columncounter +2).clearcontents
> > >
> > > where rowcounter and columncounter are locally dimmed integers with verified
> > > values (I can see them in the debug)
> > >
> > > However, I get a run time 1004 error when this line executes -- if I change
> > > the range designation above to "A1:C3" format, I don't get the error. Is it
> > > a problem that rowcounter and columncounter are locally defined?
> > >
> > > Any help or advice would be appreciated.
> > >
> > > Thanks.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson