[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Trouble with Macro and Formula


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.


1 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.

nomiosAuthor Commented:
Thanks. :)

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now