Trouble With Range Object Methods (Error 1004)
I am creating code to automate many normal functions on a spreadsheet, in order to assist personnel who may not know how to use excel particularly well. I have broken the operations into subprocedures, and will provide the two in question below. The first sub basically declares a range variable, and then assigns a range of cells to it. The problem appears to be that when I try to act on this object, things go awry. The frustrating part is that the exact same code in the sub below works!! More to the point, I had the code in the first sub working earlier, and have now apparently 'broken' something. The error message is 1001 Select method of range class failed, which I can not figure out, as the same thing works in the subsequent sub!
Please take a look, and let me know if I am missing something obvious. Thanks for your help. If you see redundancy, poor coding, etc. please feel free to point it out, as I would really like to do this the right way.
Doesn't work, why?
Sub copyclean_weekly()
Dim myweeklycell As Range 'Declares range variable
On Error GoTo errStop 'Error Handling
'Selection Code
Sheets("Weekly").Activate 'Selects sheet
Set myweeklycell = Range("A4").CurrentRegion.Offset(3, 0) 'Assigns range to object
Set myweeklycell = myweeklycell. _
Resize(myweeklycell.Rows.Count - 3, _
myweeklycell.Columns.Count)
myweeklycell.Select
myweeklycell.Clear
'Clears data and formats while leaving header rows behind
ActiveSheet.Range("A4:IV65536").Clear
ActiveSheet.Range("A4:IV65536").ClearFormats
ActiveSheet.Range("A4").Select 'Returns to starting cell
errStop:
End Sub
This works:
'11/15/02 P. Hassett
'COMPLETE
'Performs operations on "Working" spreadsheet
'Selects active cells to last row and copies values
'Clears contents afterwards
Sub copyclean_working()
Dim myworkingcell As Range 'Declares range variable
Dim blankcount As Range
'On Error GoTo errStop 'Error Handling
'Select and assign a range object named myworkingcell
'Backfill all blank values with a null indicator
Sheets("Working").Activate 'Selects sheet
Set myworkingcell = Range("A4").CurrentRegion.Offset(3, 0) 'Assigns range to object
Set myworkingcell = myworkingcell. _
Resize(myworkingcell.Rows.Count - 3, _
myworkingcell.Columns.Count)
myworkingcell.Select
BlankFill 'Call function to fill in blanks
myworkingcell.Copy Destination:=Worksheets _
("Weekly").Range("A4") 'Resizes and copies range of myworkingcell
myworkingcell.Clear 'Take out the trash
'Clears data and formats while leaving header rows behind
ActiveSheet.Range("A4:IV65536").Clear
ActiveSheet.Range("A4:IV65536").ClearFormats
ActiveSheet.Range("A4").Select 'Returns to starting cell
errStop:
End Sub
Sheets:
"Working" - where the person will enter data during the week
"Weekly" - the temporary sheet that the person will use as a report at the end of the week; gets cleared everytime it is run to have fresh sheet
"Backup" - running collection of all data entered; is appended during end of each week
1. Select all data everything on "Weekly", minus the header row,
and clear it out. Position the focus on "A4", as it is the cell immediately following the header row.
2. Select all data from "Working", fill in all blanks with "#N/A", copy data range to weekly
3. Find last row of "Backup", and advance one cell; copy values from weekly, and paste to backup
This may sound a bit ridiculous, but when you are dealing with the computer illiterate, it makes a difference. I could probably just make a written procedure, but felt like taking on a challenge. If this makes sense, what would you recommend?
I am not sure I understand what you are trying to say with the a7 reference? There is a possibility that during a week, there woudl be 1, 100, or 0 entries in this spreadsheet, so there really isn't anything magical about the references, other than A4 being the next in line after the header.
This isn't necessary, the offset and resize, just use a7's current region, if it's one row, then no error:
Sub copyclean_weekly2()
Dim myweeklycell As Range 'Declares range variable
Sheets("Weekly").Activate 'Selects sheet
Set myweeklycell = [a7].CurrentRegion
myweeklycell.Clear
'Clears data and formats while leaving header rows behind
'Range("A4:IV65536").Clear 'this clears formats, contents, etc...
[a4].Select
End Sub
What I can't figure out is why you'd take the trouble to clear a specific range, then clear out the entire sheet....
Also, I took out the selecting range business, not necessary. Hope this helps.
Dim r As Long
r = [a65536].End(xlUp).Row
Range("a4:h" & r).Select
Where you change H to the right column of your choice.
#If you have any other info about this subject , Please add it free.# |