Trouble with Macro and Formula

Posted on 2011-10-13
Last Modified: 2012-05-12

I had some help with this before and made some changes to the file. It's still not doing quite what I need the file to do.

In the post review tab, I want everything to copy over to the "reviews" tab. There is a formula in cell "AD" that I can't get to copy correctly.

In short, I want to go to "Associate Review" section and type in a name and have it auto populate the most recent 20 entries from the "Reviews" Sheet.

I also can't get the formulas in the "Monthly" and "Weekly" sections to work.


Question by:nomios
    LVL 17

    Accepted Solution

    I have made a small change to your macro code to double up the quotes as they were inside a string:
        Worksheets("Reviews").Range("X2").PasteSpecial Paste:=xlPasteValues, Transpose:=False, SkipBlanks:=False
        Worksheets("Reviews").Range("Z2").Formula = "=VLOOKUP(A2,'Associate Team'!$A:$C,2,0)"
        Worksheets("Reviews").Range("Y2").Formula = "=VLOOKUP(A2,'Associate Team'!$A:$C,3,0)"
        Worksheets("Reviews").Range("AA2").Value = Date
        Worksheets("Reviews").Range("AB2").Formula = "=MONTH(AA2)"
        Worksheets("Reviews").Range("AC2").Formula = "=WEEKNUM(AA2)"
        ' Changed this line ============================================================
        Worksheets("Reviews").Range("AD2").Formula = "=A2&"":""&COUNTIF($A$1:A2,A2)"
        Application.CutCopyMode = False
        Call ClearReview
        Worksheets("Post Reviews").Range("C17").Formula = "Optional"
        Worksheets("Post Reviews").Range("C2").Formula = "Optional"
        Worksheets("Post Reviews").Range("A4").Formula = "Optional"

    Open in new window

    I have also changed the formulas on the Associate review tab to use the INDEX function, and they now work correctly.

    I'm not quite clear what the formulas on the Monthly and Weekly tabs are supposed to be doing - they have lots of REF! errors, presumably because you have deleted rows they originally referred to. As that is quite separate from the things I fixed, can I suggest that you raise a new question, with a full explanation of what you want them to do - it's better not to try to do multiple different issues in the same thread.


    Author Closing Comment

    Thanks. :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    25th of every month 7 38
    excel forecast function 1 27
    MS Excel Cell questions 5 39
    VBA Help TT V.1 9 28
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now