It's difficult to know where this formula goes since you use Activecell.

But maybe this will help:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim ThisCol As Long

With Worksheets("sheet1")
ThisCol = 23 '???
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(.Cells(FirstRow, ThisCol), .Cells(LastRow, ThisCol)).FormulaR1C1 _
= "=SUM(RC[-4]:RC[-1])"
End With

End Sub


BSII wrote:

I am having an issue finding the right code to sum a row of numbers.
Overall, I have a row which is variable in column quantity (cell1 to cell2)
and I want to have the VBA automatically put the formula "=Sum(cell1:cell2)"
into the first free cell at the end of the row.

I can't have the macro enter in the actual summed value as we will need to
add some of the data after the code is run, so I really need the actual
"=Sum(cell1:cell2)" formula into the cell so that it caluclates on the fly.
I've tried several different combinations using R1C1 with varible offsets and
basic forumla functions using the variables as the cell address, but the
"Sum" function and R1C1 functions seem to be very specific about what it will
take in terms of variables.

I have tried the following:

sub row_totals ()

Dim TotRange as Range
Dim TotStartAdd as String
Dim TotEndAdd as String
Dim TotFirstCol as Integer
Dim TotLastCol as Integer
Dim TotRow as Integer
Dim LC as Integer

LC = 22 'last column of used data calculated elsewhere
TotRow = Activecell.row

TotFirstCol = LC - (LC-4) 'first column to be summed
TotLastCol = LC 'Last Column to be summed

TotStartAdd = Cells(TotRow, TotFirstCol).address 'Cell1 address
TotEndAdd = Cells(TotRow, TotLastCol).address 'Cell2 address

Activecell.formula = "=sum("TotStartAdd" : "TotEndAdd")"

End Sub

This doesn't work and I'm sure I'm making this much more complicated than it
needs to be. Any help would be appreciated.

--

Dave Peterson