nb-
07-27-2009, 05:19 AM
I keep getting the following error;
Method 'Range' of object '_Worksheet' failed when trying to use the
myrange.worksheet
property, and nothing I can find seems to quite address my problem. The ranges is defined as;
set myrange = ActiveWorkbook.Sheets("Sheet1").range("A11:BF11")
This error only occurs when I run the code and sheet1 is not the active sheet.
The actual code is copying down some excel formulas from one row (11) down 'lng_t_steps' rows, and deleting any past that.
Any ideas on a solution that (prefrably dont involve activating the sheet, as it will entail a lot of sheet switching in the more general version)?
Thanks.
Public Sub test()
Dim dbl_t_step As Double
Dim lng_t_steps As Long
Dim rng_start_formula As range
Dim str_range_start As String
Dim str_range_end As String
Dim temp_str As String
lng_t_steps = 10
Set rng_start_formula = ActiveWorkbook.Sheets("Sheet1").range("A11:BF11")
str_range_start = Split_text(rng_start_formula.Address, 1, ":")
str_range_end = Split_text(rng_start_formula.Offset(lng_t_steps, 0).Address, 2, ":")
'// Copy down formula
rng_start_formula.AutoFill Destination:=rng_start_formula.Worksheet.range(range(str_range_start), range(str_range_end)), Type:=xlFillDefault
'// clear extra formulas
rng_start_formula.Worksheet.range(range(range(str_range_start).Offset(lng_t _steps + 1, 0).Address), _
range(rng_start_formula.Worksheet.range(str_range_end).End(xlDown).Address) ).Clear
end sub
Method 'Range' of object '_Worksheet' failed when trying to use the
myrange.worksheet
property, and nothing I can find seems to quite address my problem. The ranges is defined as;
set myrange = ActiveWorkbook.Sheets("Sheet1").range("A11:BF11")
This error only occurs when I run the code and sheet1 is not the active sheet.
The actual code is copying down some excel formulas from one row (11) down 'lng_t_steps' rows, and deleting any past that.
Any ideas on a solution that (prefrably dont involve activating the sheet, as it will entail a lot of sheet switching in the more general version)?
Thanks.
Public Sub test()
Dim dbl_t_step As Double
Dim lng_t_steps As Long
Dim rng_start_formula As range
Dim str_range_start As String
Dim str_range_end As String
Dim temp_str As String
lng_t_steps = 10
Set rng_start_formula = ActiveWorkbook.Sheets("Sheet1").range("A11:BF11")
str_range_start = Split_text(rng_start_formula.Address, 1, ":")
str_range_end = Split_text(rng_start_formula.Offset(lng_t_steps, 0).Address, 2, ":")
'// Copy down formula
rng_start_formula.AutoFill Destination:=rng_start_formula.Worksheet.range(range(str_range_start), range(str_range_end)), Type:=xlFillDefault
'// clear extra formulas
rng_start_formula.Worksheet.range(range(range(str_range_start).Offset(lng_t _steps + 1, 0).Address), _
range(rng_start_formula.Worksheet.range(str_range_end).End(xlDown).Address) ).Clear
end sub