+ Reply to Thread
Results 1 to 2 of 2

VBA Q- Entering Formula through recorder!

  1. #1
    Registered User
    Join Date
    07-06-2011
    Location
    Malta
    MS-Off Ver
    Excel 2003
    Posts
    63

    VBA Q- Entering Formula through recorder!

    Hi All,

    Seeking some excel Guru help!

    I'm pretty new to VBA, but have a lot of experience using excel.

    I have a sheet with two sheets;

    1. PASTE
    2. ANALYSIS

    PASTE LOOKS LIKE THIS -

    Resolved Date Country inbox
    03/06/2013 Info-it
    03/06/2013 Info-it
    03/06/2013 Info-no
    03/06/2013 Info-fi
    03/06/2013 Info-no
    03/06/2013 Info-dk
    03/06/2013 Info-uk
    03/06/2013 Info-nl
    03/06/2013 Info-bg
    03/06/2013 Info-se
    03/06/2013 Info-no
    03/06/2013 Info-nl
    03/06/2013 Info-se
    03/06/2013 Info-dk
    03/06/2013 Info-nl
    03/06/2013 Info-se
    03/06/2013 Info-dk
    03/06/2013 Info-no
    03/06/2013 Info-nl
    03/06/2013 Info-it
    03/06/2013 Info-no
    03/06/2013 Info-no
    03/06/2013 Info-se
    03/06/2013 Info-se
    03/06/2013 Info-it
    03/06/2013 Info-pl
    03/06/2013 Info-no
    03/06/2013 Info-nl
    03/06/2013 Info-it
    03/06/2013 Info-uk
    03/06/2013 Info-it
    03/06/2013 Info-pl

    Now in reality the sheet has more rows and more columns, however for this example i only need this,

    In Analysis tab there are
    DATE
    03/06/2013
    04/06/2013
    05/06/2013
    06/06/2013
    07/06/2013
    08/06/2013
    09/06/2013

    Dates,

    Now in B2 I have a rather complicated SUM, using COUNTIFS, which only take corresponding countries for the date;

    =SUM(COUNTIFS(PASTE!B:B,"Info-no",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-fi",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-uk",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-nl",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-bg",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-se",PASTE!A:A,ANALYSIS!A2),COUNTIFS(PASTE!B:B,"Info-pl",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-de",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-hu",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-lt",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-ru",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-pt",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-cz",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-gr",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-lv",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-ro",PASTE!A:A,ANALYSIS!A2), COUNTIFS(PASTE!B:B,"Info-fr",PASTE!A:A,ANALYSIS!A2))

    This sum works fine when pasted and dragged down.

    My problem!!

    If i record a macro and paste this into recorder and run the macro i get an;

    application-defined or object-defined error

    !! WHY?! Can anyone please help!

    I have attached file (see macro book )
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-06-2011
    Location
    Malta
    MS-Off Ver
    Excel 2003
    Posts
    63

    Re: VBA Q- Entering Formula through recorder!

    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
    "=SUM(COUNTIFS(PASTE!C,""Info-no"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-fi"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-uk"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-nl"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-bg"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-se"",PASTE!C[-1],ANALYS" & _
    "C[-1]),COUNTIFS(PASTE!C,""Info-pl"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-de"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-hu"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-lt"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-ru"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-pt"",PASTE!C[-1],ANALY" & _
    "C[-1]), COUNTIFS(PASTE!C,""Info-cz"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-gr"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-lv"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-ro"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]), COUNTIFS(PASTE!C,""Info-fr"",PASTE!C[-1],ANALYSIS!R[-1]C[-1]))"
    Range("B4").Select

    is the VBA code
    Last edited by aborg88; 06-13-2013 at 04:23 AM.

+ 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