# Problems using the FormulaR1C1 Property

#### gr8giz

##### New Member
Hi,

I am having issues using the formulaR1C1 property. Actually, I haven't been able to figure out how to use variables inside of this property to refer to cells.

What I am trying to accomplish is the following:
I have data arranged by date in columns and a key index in rows. For a user specified week end date, I want to calculate the average for a week's worth of data.
Using the user specified date, I search for the date in the column headings and save the column number in a variable temp.

How can I use temp inside the formulaR1C1 property? I also couldnt understand how to use the Formula property.

Here is the code

Rich (BB code):
``````
With temp_file.Sheets("Fwd_SBEVM_MB")
.Cells(3, 33).FormulaR1C1 = "=AVERAGE(RC[33-temp-6]:RC[33-temp]"
End With
``````

I get error 1004 application defined or object defined error.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Norie

##### Well-known Member
This perhaps?
Rich (BB code):
``.FormulaR1C1 = "=AVERAGE(RC[" & 33 - temp - 6 & "]:RC[" & 33 - temp & "]"``

#### RoryA

##### MrExcel MVP, Moderator
Rich (BB code):
``````With temp_file.Sheets("Fwd_SBEVM_MB")
.Cells(3, 33).FormulaR1C1 = "=AVERAGE(RC[" & 27-temp & "]:RC[" & 33-temp & "])"
End With``````
should do it I think.

#### gr8giz

##### New Member
I am still getting the same errors. Also, could you guys explain the logic behind this syntax?

#### RoryA

##### MrExcel MVP, Moderator
Which one did you try? Mine has an additional closing parenthesis that was missing from your formula.

#### gr8giz

##### New Member
I tried your code with the paranthesis but still seeing the application defined error.

#### Norie

##### Well-known Member
gr8giz

What is the value of temp when you get the error?

Rory

Good spot.

I just tried fixing the concatenation syntax.

#### gr8giz

##### New Member
Oops!! Got it. I made a mistake with the variable temp. Now it works. Thanks a lot. But could you please explain the syntax to me, especially the use of & and ". I appreciate your help.

Replies
11
Views
2K
Replies
16
Views
2K
Replies
4
Views
705
Replies
5
Views
344
Replies
0
Views
336

1,190,781
Messages
5,982,873
Members
439,803
Latest member
sushilneupane

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

### Which adblocker are you using?

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

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