+ Reply to Thread
Results 1 to 11 of 11

how to create formula with vba

  1. #1
    Registered User
    Join Date
    07-01-2009
    Location
    Croatia
    MS-Off Ver
    Excel 2003
    Posts
    63

    how to create formula with vba

    Hi!

    In one sheet I have data, and in another I calculate minimum, maximum and average of that data for last two months. At the beginning of the new month I manually change the range for the new month. can I do that somehow automatically with vba?

    so, in example, the december should became january with minimum, maximum and average from Sheets("data").Range("b255:b275")
    Attached Files Attached Files
    Last edited by cassiopea; 01-05-2012 at 03:35 AM.

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,376

    Re: how to create formula with vba

    maybe so (see attachment)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-01-2009
    Location
    Croatia
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: how to create formula with vba

    this works in example file. but in real file I have several years, so this formula would, I think, consider Decembers from all years and not just from last that I need.

    and my question was is it possible to create ie. to calculate this formula with vba, because in the example you gave me,I still need to manually change formula for next month.
    Last edited by cassiopea; 01-04-2012 at 03:32 AM.

  4. #4
    Registered User
    Join Date
    07-01-2009
    Location
    Croatia
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: how to create formula with vba

    Hi!
    Is there any way I can rewrite the following code so it works but not in R1c1 notation.

    Please Login or Register  to view this content.
    I also tried this:
    Please Login or Register  to view this content.
    but it is not working.
    Can this be done differently?

  5. #5
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,376

    Re: how to create formula with vba

    And if so? Formulas in the green cells recalculate the maximum, minimum and average values over the last two months, depending on the date in cell F1.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-01-2009
    Location
    Croatia
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: how to create formula with vba

    When I open file error #NAME? appears in sheet "count" cells A1, A2, and in the whole column C.

    =ДАТАМЕС(F1;-2) I think it is because of this formula. I don't have it in excel 2003

  7. #7
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,376

    Re: how to create formula with vba

    Sorry, this function from Russian local version. Write down the formula in cell A1:
    =EDATE(F1,-2)
    in cell A5:
    =EDATE(F1,-1)

    This function is available in the Excel 2003 after connecting Analysis ToolPack add-in.

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: how to create formula with vba

    PHP Code: 
    Sheets("count").Range("C5")="=MIN(data!B2241:B2261)" 



  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: how to create formula with vba

    PHP Code: 
    Sheets("count").Range("C5")="=MIN(data!B2241:B2261)" 

  10. #10
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,376

    Re: how to create formula with vba

    ----------
    Last edited by nilem; 01-04-2012 at 03:50 PM. Reason: repetition

  11. #11
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,376

    Re: how to create formula with vba

    as an option (with macro and formulas)
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1