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

parkerbelt

Active Member
Joined
May 23, 2014
Messages
377
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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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 [COLOR=#333333] "=SUM(RC[-54+" & CurrentWeekNumber & "+1],RC[-54+" & CurrentWeekNumber & "+2],RC[-54+" & CurrentWeekNumber & "+3],RC[-54+" & CurrentWeekNumber & "+4])"[/COLOR]
Compare the results of the Message Box to what you recorded. Does the code look the same?
 
Upvote 0
Try this...

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

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top