How do i use mac to place formula into sheet that use vb ref

[DevintheDude]

lastcell = Cells(Rows.Count, 1).End(xlUp).row
last = lastcell + 1
Cells(last, "R").Formula = "=SUM(""R4:R""& last)"


I have the formula above the only problem with it is that it puts the formula as is last's value is not being used like i want it to is their another way or how can i make last the value first before it sends to the sheet

 

[mikerickson]

This isn't specific to a Mac.

It looks like you have data in column R and would like a formula summing everything in that column (below row 3) placed at the end of the column.
If so, this should do it

Dim xRange As Range

Set xRange = Cells(.Rows.Count, 18).End(xlUp)
xRange.Offset(1, 0).FormulaR1C1 = "=SUM(R4C:R[-1]C)"

 

[DevintheDude]

No this is not specific to a MAC, mac in the quesiton stands for macro if i wrote it out it wouldn't fit.

But yeah that examples work just perfect how does R[-1] know to go to the last cell
why do you offset row 1 col 1 what does that do other than that i understand

 

[DevintheDude]

can you do it with columns

 

[mikerickson]

R[-1] is R1C1 notation for "this row minus 1".
The variable xRange is set to the last filled cell in the column (at the time that that line is excicuted.) . I want the formula put one cell below that, so I Offset(1,0).

And yes, the code can be modified to do columns as well as cells.