Results 1 to 4 of 4

VBA - I can't get the syntax for a simple formula correct.

This is a discussion on VBA - I can't get the syntax for a simple formula correct. within the Excel Questions forums, part of the Question Forums category; I'm trying to use VBA to enter a simple formula into cell BM10 that gives me a 4 week forecast. ...

  1. #1
    Board Regular
    Join Date
    May 2014
    Posts
    318

    Default VBA - I can't get the syntax for a simple formula correct.

    I'm trying to use VBA to enter a simple formula into cell BM10 that gives me a 4 week forecast.

    I want to take the number that the user enters(CurrentWeekNumber) and add it to RC[-54] plus 1, plus 2 and plus 3, so the formula gets the values from 4 different cells and adds them together.

    Here is the code that doesn't work that I'm using:

    ActiveCell.FormulaR1C1 = "=SUM(RC[-54+" & CurrentWeekNumber & "+1],RC[-54+" & CurrentWeekNumber & "+2],RC[-54+" & CurrentWeekNumber & "+3],RC[-54+" & CurrentWeekNumber & "+4])"

    Here's the error that I'm getting:

    Run-time error '1004':

    Application-defined or object-defined error

    This should be a simple one, I just can't get the syntax right.
    Thanks!

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    38,663

    Default Re: VBA - I can't get the syntax for a simple formula correct.

    One way to figure this out is to turn on the Macro Recorder and record yourself typing in the formula on the sheet manually, and then stop the recorder.
    Now, if you view the recorded code, you will see exactly what your VBA code needs to look like.

    Now, take the formula you are building and temporarily put it in a Message Box instead, i.e.
    Code:
    MsgBox  "=SUM(RC[-54+" & CurrentWeekNumber & "+1],RC[-54+" & CurrentWeekNumber & "+2],RC[-54+" & CurrentWeekNumber & "+3],RC[-54+" & CurrentWeekNumber & "+4])"
    Compare the results of the Message Box to what you recorded. Does the code look the same?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,335

    Default Re: VBA - I can't get the syntax for a simple formula correct.

    Try this...

    ActiveCell.FormulaR1C1 = "=SUM(RC[" & (-54 + CurrentWeekNumber + 1) & "]:RC[" & (-54 + CurrentWeekNumber + 4) & "])"
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  4. #4
    Board Regular
    Join Date
    May 2014
    Posts
    318

    Default Re: VBA - I can't get the syntax for a simple formula correct.

    Quote Originally Posted by AlphaFrog View Post
    Try this...

    ActiveCell.FormulaR1C1 = "=SUM(RC[" & (-54 + CurrentWeekNumber + 1) & "]:RC[" & (-54 + CurrentWeekNumber + 4) & "])"

    That worked.
    Thank you!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com