VBA Error

Posted on 2011-05-13
Last Modified: 2012-05-11

I have this code in the VBA Editor

ActiveCell.FormulaR1C1 = "=DATE(YEAR(Update!AB1),MONTH(Update!AB1),0)"

It produces an error in excel, the formula it drops out is


I notice if i take the apostrophes away from AB1, the formula works, how can i fix this in the editor?

Question by:Seamus2626
    LVL 50

    Accepted Solution


    Don't use the ActiveCell.FormulaR1C1 if you reference cells with the A1 style. Instead use

    ActiveCell.Formula = "=DATE(YEAR(Update!AB1),MONTH(Update!AB1),0)"

    and the formula in the cell will be fine.

    cheers, teylyn
    LVL 33

    Assisted Solution

    Put .Formula instead, like this:

    ActiveCell.Formula= "=DATE(YEAR(Update!AB1),MONTH(Update!AB1),0)"


    Author Closing Comment

    Thanks guys

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    728 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

    17 Experts available now in Live!

    Get 1:1 Help Now