Young whiz brain required
Results 1 to 6 of 6

Thread: Young whiz brain required
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2005
    Location
    Tasmania
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Young whiz brain required

    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.

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,654
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Young whiz brain required

    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

    Code:
    Application.OnTime Now + TimeValue("01:00"), "MM1"
    Then in the Thisworkbook module add the following macro.

    Code:
    Private Sub Workbook_Open()
    Call MM1
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,654
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Young whiz brain required

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

    https://www.youtube.com/watch?v=ii59euBCux0
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,518
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Young whiz brain required

    Try

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


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

  5. #5
    Board Regular
    Join Date
    Nov 2005
    Location
    Tasmania
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Young whiz brain required

    Magic Mike Magic!!!

    Thank you very much working like a charm now.

  6. #6
    Board Regular
    Join Date
    Nov 2005
    Location
    Tasmania
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Young whiz brain required

    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

Some videos you may like

User Tag List

Tags for this Thread

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
  •