Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: OnTime and error 1004

  1. #1
    Board Regular
    Join Date
    Sep 2005
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default OnTime and error 1004

    Hi everyone.
    Sorry if this has been asked again. Took a look but didnt find anything that swits me.

    I want to run the same macro every 5 sec. So,
    after the requirments are met the macro "CurrentMacro" is fired. It is:

    Sub LayFormulaMacro1()
    '
    ' my code here, some if statements for exit sub and a couple of Sheet2!Range(A1)=Sheet1Range(B2)
    '
    Application.OnTime Now() + TimeValue("00:00:05"), "CurrentMacro"

    but it fails and gives me the error:

    Run-time error '1004'
    Method 'OnTime' of object'_Application' failed

    I press the end button at the messege box and then the 'Run Sub' button (bassically i run manually the macro) and now the macro works fine.
    Dont get it , why it fails first place?
    Debugger highlights the row "Application.OnTime ......."

  2. #2
    Board Regular QuietRiot's Avatar
    Join Date
    May 2007
    Location
    Boston
    Posts
    971
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OnTime and error 1004

    try it like this..

    in the thisworkbook part place these two

    Code:
    Private Sub
      Workbook_BeforeClose(Cancel As Boolean)
       Application.OnTime dTime, "CurrentMacro", , False
    End Sub
    
    Private Sub Workbook_Open()
      Application.OnTime Now + TimeValue("00:00:05"), "CurrentMacro"
    End Sub
    Now in the standard module (Insert>Module) place this;

    Code:
    Public dTime As Date
    
    Sub CurrentMacro()
    dTime = Now + TimeValue("00:00:05")
    Application.OnTime dTime, "CurrentMacro"
    
    'rest of your code is here now
    
    End Sub
    -Qu!et Ri0t

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OnTime and error 1004

    Hello,

    Nope, nothing's jumping out at me...

    A few questions:
    1. Why are you using the Ontime Method?
    2. Where is CurrentMacro located? (Module name/type)
    3. Can you post CurrentMacro?

  4. #4
    Board Regular
    Join Date
    Sep 2005
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OnTime and error 1004

    Hi everyone.
    In sheet1 Im getting data from external source (every 0,5 sec).So a worksheek change event checks for right conditions every 0,5 sec (lets say if the formula in sheet2!Range(A1) is RECORDING ( it will be for some time)

    1. I want a macro to be run every 5 sec. I want to copy some of the new data every 5 sec ,while sheet2!Range(A1) is RECORDING (the right conditions)
    2. The module is number 7 under VGAProjdect . Sorry dont know what are you looking for- I dont understand.
    3. Here is the code

    Code:

    Sub LayFormulaMacro1()

    If Sheets("1").Range("AC47").Value = "RECORDED" Then Exit Sub
    If Sheets("1").Range("AB47").Value = "NOT RECORDING" Then Exit Sub
    If Sheets("Layformula temp").Range("B2") > 220 Then Run "CopyFinalLayformulaInformation"
    If Sheets("Layformula temp").Range("B2") > 220 Then Exit Sub
    If Sheets("1").Range("AC47").Value = "RECORDING" Then GoTo 89
    Run "NamesforLayformula"

    89 LastFreeRowBackOdds = Sheets("Layformula temp").Cells(2, 2).Value

    Sheets("Layformula temp").Cells(42, Sheets("Layformula temp").Cells(2, 2).Value) = Sheets("1").Range("AB19").Value
    Sheets("Layformula temp").Range(Sheets("Layformula temp").Cells(43, Sheets("Layformula temp").Cells(2, 2).Value + 1), Sheets("Layformula temp").Cells(57, Sheets("Layformula temp").Cells(2, 2).Value + 1)).Value = Sheets("1").Range("F5:F19").Value

    Sheets("Layformula temp").Select
    Application.OnTime Now() + TimeValue("00:00:05"), "LayFormulaMacro1"

    If Sheets("1").Range("AC47").Value = "RECORDED" Then Exit Sub
    Sheets("1").Range("AC47").Value = "RECORDING"

    End Sub


    There used to be some "code" buttons, where are they?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •