I cant the below code to work as it fails if I do not include the activate/select instruction in the code. Portion highlighted in red are the statements where it fails -
Sub data_1min()
rNum_1min = Sheets("data_1min").Cells(1, 1).Value + 1
Sheets("data_1min").Cells(rNum_1min, 2).NumberFormat = "@"
Sheets("data_1min").Cells(rNum_1min, 2).Value = "@" & Application.WorksheetFunction.Text(Application.WorksheetFunction.MRound(Time, "00:01:00"), "hh:mm:ss")
'toFind = Application.WorksheetFunction.MRound(Sheets("data_1min").Cells(rNum_1min, 2).Value, "00:01:00")
toFind = Sheets("data_1min").Cells(rNum_1min, 2).Value
open_1min = Application.WorksheetFunction.Match(toFind, Sheets("Data").Range("C:C"), 0)
count_1min = Application.WorksheetFunction.CountIf(Sheets("Data").Range("C:C"), toFind)
close_1min = Sheets("Data").Cells(open_1min + count_1min - 1, 6).Value
high_1min = Application.WorksheetFunction.Max(Sheets("Data").Range(Cells(open_1min, 6), Cells(open_1min + count_1min - 1, 6)))
low_1min = Application.WorksheetFunction.Min(Sheets("Data").Range(Cells(open_1min, 6), Cells(open_1min + count_1min - 1, 6)))
Sheets("data_1min").Cells(rNum_1min, 4).Value = Sheets("Data").Cells(open_1min, 6).Value
Sheets("data_1min").Cells(rNum_1min, 5).Value = high_1min
Sheets("data_1min").Cells(rNum_1min, 6).Value = low_1min
Sheets("data_1min").Cells(rNum_1min, 7).Value = close_1min
Application.OnTime Application.WorksheetFunction.MRound(Format(Time, "hh:mm:ss"), "00:01:00") + TimeValue("00:01:00"), "data_1min"
End Sub
Sub data_1min()
rNum_1min = Sheets("data_1min").Cells(1, 1).Value + 1
Sheets("data_1min").Cells(rNum_1min, 2).NumberFormat = "@"
Sheets("data_1min").Cells(rNum_1min, 2).Value = "@" & Application.WorksheetFunction.Text(Application.WorksheetFunction.MRound(Time, "00:01:00"), "hh:mm:ss")
'toFind = Application.WorksheetFunction.MRound(Sheets("data_1min").Cells(rNum_1min, 2).Value, "00:01:00")
toFind = Sheets("data_1min").Cells(rNum_1min, 2).Value
open_1min = Application.WorksheetFunction.Match(toFind, Sheets("Data").Range("C:C"), 0)
count_1min = Application.WorksheetFunction.CountIf(Sheets("Data").Range("C:C"), toFind)
close_1min = Sheets("Data").Cells(open_1min + count_1min - 1, 6).Value
high_1min = Application.WorksheetFunction.Max(Sheets("Data").Range(Cells(open_1min, 6), Cells(open_1min + count_1min - 1, 6)))
low_1min = Application.WorksheetFunction.Min(Sheets("Data").Range(Cells(open_1min, 6), Cells(open_1min + count_1min - 1, 6)))
Sheets("data_1min").Cells(rNum_1min, 4).Value = Sheets("Data").Cells(open_1min, 6).Value
Sheets("data_1min").Cells(rNum_1min, 5).Value = high_1min
Sheets("data_1min").Cells(rNum_1min, 6).Value = low_1min
Sheets("data_1min").Cells(rNum_1min, 7).Value = close_1min
Application.OnTime Application.WorksheetFunction.MRound(Format(Time, "hh:mm:ss"), "00:01:00") + TimeValue("00:01:00"), "data_1min"
End Sub