Can't get this to work

  • Thread starter Thread starter ranswrt
  • Start date Start date
R

ranswrt

The following code worked until I changed how I started it. It originally
started with button on a worksheet (not a control button). I changed it to a
control button now it doesn't work. Everything else in the procedure works
fine. The code is:

Set ycell = Sheets("Estimates DB").Range("estdbno")
Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng"

The error I get is:

Method 'Range" of object '_Worksheet' failed.

Everything is set right. ycell as range, the sheet name and cell name are
correct. I was wondering if it could be because I changed it to a control
button and moved the procedure from a module to to an excel object. Any help
on this would be greatly appreciated.
Thanks
 
What does "It don't work" mean? It won't start when the button is clicked?
It gives an error message? What specifically don't work?
 
Your issue revolves around the sheet reference. Ycell is on sheet Estimates
DB but when you use Range at the beginning of
Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng"

if is the same as
Activesheet.Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name =
"estdbnorng"

which is probably not correct as Estimates DB is probably not the active
sheet. Try this...

with Sheets("Estimates DB")
Set ycell = .Range("estdbno")
..Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng"
end with
 
The procedure work when the button is clicked until it gets to this line and
I get the error message.

Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng"
 
Probably u can do is:
Sheets("Estimates DB").Select
Set ycell = Range("estdbno")
Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng"

coz once i had a similar problem with using sheet("sheetname").range
 
While that will probably work it is a very bad practice to select sheets or
ranges. It makes the code slower to execute. It bloats the code and it makes
it much more suseptible to failure in the future as you modify code and
potentially change the active sheet around. Just my 2 cents...
 
Back
Top