Problems using the FormulaR1C1 Property

[gr8giz]

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

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.

 

[Norie]

This perhaps?

.FormulaR1C1 = "=AVERAGE(RC[" & 33 - temp - 6 & "]:RC[" & 33 - temp & "]"

 

[RoryA]

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]

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

 

[RoryA]

Which one did you try? Mine has an additional closing parenthesis that was missing from your formula.

 

[gr8giz]

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

 

[Norie]

gr8giz

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

Rory

Good spot.

I just tried fixing the concatenation syntax.

 

[gr8giz]

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.