Young whiz brain required

[mikemcbain]

I have an extensive macro that builds a database from a number of daily files but it requires me to type a negative number into cell C23 and then I must click a button to run my macro.

These negative numbers represent each past day eg. -2 produces the info for the day before yesterday and -365 produces the info for this day last year etc .

I want the macro to run unattended overnight but for the life of me I cannot work out how to tell it to change C23 from say -33 to -34 and then re-run the macro?

Sounds so simple but my old brain cannot do it so perhaps some young whiz can help me?

Mike.

 

[Michael M]

Not sure that I follow, but could you not simply run a timer macro say every hour or 2 hours
Maybe

If your macro is called MM1

Insert in 1st line of code in MM1

Application.OnTime Now + TimeValue("01:00"), "MM1"

Then in the Thisworkbook module add the following macro.

Private Sub Workbook_Open()
Call MM1
End Sub

 

[Michael M]

Also, have you considered using Windows Task Scheduler ??
Here's a you tube of it's use

https://www.youtube.com/watch?v=ii59euBCux0

 

[mikerickson]

Try

For i = -33 to -34 Step -1
    Range("C3").Value = i
    Call yourMacro
Next i

or

With Range("C3")
    .Value = -32
    Do
        .Value = .Value - 1
        Call YourMacro
    Loop Until .Value = -34
End With

 

[mikemcbain]

Magic Mike Magic!!!

Thank you very much working like a charm now.

 

[mikemcbain]

And a thank you to Michael M too!
Your suggestions have prompted me to look deeper for a couple of other problems I have.

Best regdrs

Mike