I am getting Error 1004 Select Method of Range Class failed.

Question

Program as attached runs fine until I try to delete the part of the spreadsheet I no longer want so I can move to the next day's data. Error 1004 as in the title.

Offending line is Sheets("Sheet1").Range("A2:C49").Select ' Gives error 1004 Select method of Range Class failed.

(My comment added for clarity)

It works fine as a stand alone Sub but as I want to repeat it multiple times it's in a For/Next loop - I am hoping someone can tell me why it doesnt work! Attached code of complete project below, without the data.

  Option Explicit
Dim Offpk As Single
Dim Peak As Single
Dim Days As Integer
Dim Mydate As String
Dim Rownum As Integer


Sub Octopus()

End Sub
Sub Setup()
' Do Headers and resize colums to fit data.
Peak = InputBox("Give peak price?")
Offpk = InputBox("Give off peak price?")
Days = InputBox("How many days?")
Worksheets(2).Range("A1").Value = "Date"
Worksheets(2).Range("B1").Value = "Off peak usage"
Worksheets(2).Range("C1").Value = "Off peak costs"
Worksheets(2).Range("D1").Value = "Peak usage"
Worksheets(2).Range("E1").Value = "Peak costs"
Worksheets(2).Range("F1").Value = "Total cost"
Worksheets(2).Cells.EntireColumn.AutoFit

End Sub

Sub FindReplaceall()
Dim sht As Worksheet
Dim fnd As String * 1
Dim rplc As String * 1
' Replace T with space and remove unwanted data from times

fnd = "T"
rplc = " "
' Store a specific sheet to a variable
Set sht = Sheets("Sheet1") ' Needs to be changed for correct sheet
' Now replace Ts
sht.Range("2:1046576").Replace What:=fnd, Replacement:=rplc, Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
sht.Range("2:1046576").Replace What:=":00+00:00", Replacement:="", Lookat:=xlPart, SearchOrder:=xlByRows
sht.Range("2:1046576").Replace What:=":00+01:00", Replacement:="", Lookat:=xlPart, SearchOrder:=xlByRows
End Sub

 Sub Calculations()

 Dim Rownum As Integer
 ' Offset added as Rownum
 For Rownum = 0 To Days
 Worksheets(2).Range("B2").Offset(Rownum).Value = Application.WorksheetFunction.Sum(Worksheets(1).Range("A3:A10")) ' Add off peak values
 Worksheets(2).Range("D2").Offset(Rownum).Value = Application.WorksheetFunction.Sum(Worksheets(1).Range("A11:A49")) ' Add Peak values
 Worksheets(2).Range("D2").Offset(Rownum).Value = Worksheets(2).Range("D2").Value + Worksheets(1).Range("A2").Value ' Last peak value added
 Worksheets(2).Range("C2").Offset(Rownum).Value = Worksheets(2).Range("B2").Value * Offpk ' Off peak cost
 Worksheets(2).Range("E2").Offset(Rownum).Value = Worksheets(2).Range("D2").Value * Peak 'Peak Cost
 Worksheets(2).Range("F2").Offset(Rownum).Value = Worksheets(2).Range("C2").Value + Worksheets(2).Range("E2").Offset(Rownum).Value  ' Total cost
 Mydate = Worksheets(1).Range("B2").Value ' Pull date from column B
Worksheets(2).Range("A2").Value = Left(Mydate, 11) 'Drop date value into Column A in test worksheet.
Worksheets(2).Cells.EntireColumn.AutoFit

' Delete first 49 rows

Sheets("Sheet1").Range("A2:C49").Select ' Gives error 1004 Select method of Range Class failed
     Selection.Delete Shift:=xlUp


Next Rownum ' Do next day(s)

End Sub
Sub Dailyroutine()
 Call Setup             'Run once only
 Call FindReplaceall    'Run once only
 Call Calculations
 End Sub
  
 
I'd appreciate advice - thanks in advance.
Brian

Answer 1

Hi,

Here's what I found on the internet that may help you out-

https://community.spiceworks.com/how_to/195135-how-to-fix-run-time-error-1004-in-excel

https://excelchamps.com/vba/runtime-error-1004/

Best Regards.