FormulaR1C1 wrong?

[NinaE_11]

Hello,
I'm able to step through this code using F8, however, when running the sub-routine by pressing F5, it chokes on the last line: I can't figure out what the error is: My data starts in F8 and goes down to rows 62...I want the subtotal of that list for rows from the bottom of list. Thank you for any insight!

Dim Total As Long
Dim lastrow As Long
Dim firstrow As Long
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets(1)

ws.Activate


'Total Market Values and Percentages
Total = Range("F8").End(xlDown).Row
Range("F" & Total).Offset(4).FormulaR1C1 = "=sum(r8c:r[-4]c)"

 

[MARK858]

r will be the row of whatever cell is active when the macro runs as you haven't moved the active cell to the last cell

 

[MARK858]

Try (untested as on my phone)...
"=sum(r8c:r" & Total & "c)"

 

[NinaE_11]

Try (untested as on my phone)...
"=sum(r8c:r" & Total & "c)"

That didn't work... I'm not able to crack this yet.....

 

[Fluff]

What do you mean by

it chokes on the last line

 

[Sektor]

And what your error is?

 

[MARK858]

Actually I wasn't thinking straight in post 2 as the r will be relative to the cell the formula resides in, both the original formula and the one I posted work for me when using continuous data.
Do you have continuous data i.e. no blank cells in column F? and is the sheet that you are working on the tab that is the furthest left as you look at it?

Because of the above both the questions asked in posts 5 & 6 still stand.

 

[NinaE_11]

Actually I wasn't thinking straight in post 2 as the r will be relative to the cell the formula resides in, both the original formula and the one I posted work for me when using continuous data.
Do you have continuous data i.e. no blank cells in column F? and is the sheet that you are working on the tab that is the furthest left as you look at it?

Because of the above both the questions asked in posts 5 & 6 still stand.

Continuous Data: Starting in Cell F8, I have no blank cells in the F column until the end of the list. I thought I was telling it to start at Row8, in the same column with the"=sum(r8c...." ?

By 'choking' - I mean that I cannot get the sub routine to run on it's own using F5; I can step through it, and the formula acts as expected (using F8), but I'm trying to link a number of Private Sub Routines together to call upon individual sub routines to create a larger script.
The error is a mismatched error; Watch window Value says "<Application-defined or object-defined error>"

 

[MARK858]

Do you have the code in a regular module or a sheet module?

 

[RoryA]

What is the actual (verified, not assumed ) value of Total when the error occurs? My guess would be that you are doing something that refreshes data and it hasn't populated when this code runs so Total is the last row of the sheet, and you can't offset 4 from that.