0

This method should only run during the defined period and then start again from the beginning on the following day. Does anyone have an idea why the method does not start by itself the next day? I have defined that it should start again at 7:50 a.m.

If  Time > TimeValue("18:00:00") Or Time < TimeValue("07:49:00") Then
    Application.Wait "07:50:00"
Else

Edit: Here is the complete code but without variable declaration. The aim of the macro is to take a value once every hour and copy it into the defined cell.

If Time > TimeValue("18:00:00") Or Time < TimeValue("07:49:00") Then
    Application.Wait "07:50:00"
Else

    If MyTime >= Format(Time, "hh:00:00") And MyTime < Format(Time, "hh:59:59") Then
        MyTime = Format(Time, "hh:00:00")
        
    End If
    
    Set rngData = Tabelle5.Range("A1:L283")
    
    ReDim arr(1 To rngData.Rows.Count, 1 To rngData.Columns.Count)
    arr = rngData.Value
    
    With Tabelle5.Range("A1:L1")
        Set MyColumn = .Find(MyTime, LookIn:=xlValues)
        On Error Resume Next
    End With
    
    For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i, 1) = MyDate Then
            x = i
            Exit For
        End If
    Next i
    
    Set MyAddress = Cells(x, MyColumn.Column)
    On Error Resume Next
    
    Tabelle1.Range("C11").Copy
    Tabelle5.Range(MyAddress.Address).PasteSpecial xlPasteValues
    
    Tabelle4.Range("B2") = Now
    
    Application.OnTime Now() + TimeValue("01:00:00"), "DatenabrufCAL23"
    
End If

Thanks for your help!

Pottbayer
  • 1
  • 2
  • I reckon we need to see more of your code. I'm guessing the context is extremely important in this scenario. – Skin Mar 28 '22 at 09:28
  • Just added the full code to my question. Thanks! – Pottbayer Mar 28 '22 at 09:36
  • Stupid question, have you debugged it and does it get to this line ... `Application.Wait "07:50:00"`? – Skin Mar 28 '22 at 09:39
  • Yes, I have already tried that. I have also experimented with different time periods. During the experiments, the programme jumped to this code line. – Pottbayer Mar 28 '22 at 09:42
  • But once it's done waiting it then skips to the end because the rest of the code is in the `Else`. So is there more to this? – Simon Mar 28 '22 at 09:47
  • The programme should retrieve market prices in order to be able to calculate market volatility etc. later. It should run 365 days a year between 07:50 and 18:00. On weekends, the markets are closed, which is why it cannot retrieve prices or the value is = 0. Does this help? After it's done waiting it should not jump to the end, it should jump to the start and check if it's in the right time period an then start copying the needed value. – Pottbayer Mar 28 '22 at 09:54
  • I have seen in the past that if the PC goes into a locked or suspended state for a while then macros seem to stop firing. You may want to consider an alternative solution such as using the task scheduler to call your macros at a specific time. example here: https://stackoverflow.com/q/22771185/3688861 – Tragamor Mar 28 '22 at 12:51

0 Answers0