OnTime and error 1004

[alkarkar]

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 ......."

 

[QuietRiot]

try it like this..

in the thisworkbook part place these two

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;

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

 

[NateO]

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?

 

[alkarkar]

Hi everyone.
In sheet1 I’m 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 don’t know what are you looking for- I don’t 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?