Sum row using variable
 

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

Sum row using variable
 

Can I use a row to find that last used column--like if row 1 contains headers?

If yes:

Option Explicit
Sub testme()

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

With Worksheets("sheet1")
'I used row 1 to find the last used column.
ThisCol = .cells(1, .columns.count).end(xltoleft).column + 1
FirstRow = 2 'headers in row 1???
'and I used column A to find the last used row.
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(.Cells(FirstRow, ThisCol), .Cells(LastRow, ThisCol)).FormulaR1C1 _
= "=SUM(RC5:RC[-1])"
End With

End Sub

=sum(rc5:rc[-1]) in R1C1 notation
RC5 is the same row, column 5 (E)
RC[-1] is the same row, one column to the left of the cell with the formula



BSII wrote:

Hi Dave,

Thanks for the help. Essentially, I have a row of numbers always starting
in column E and with varying lengths. For example, on one sheet I might have
data in E9-L9 and I want to put the sum into M9 (right after the last set of
data). So, M9 = sum(E9:L9).

Another sheet might have data in E30-U30 and the formula would go into V30
(V30-Sum(E30:U30).

Any other thoughts?
BSII (Mike Lindauer)

"Dave Peterson" wrote:

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


--

Dave Peterson

Sum row using variable
 

Dave - that did it, thanks!!

"Dave Peterson" wrote:

Can I use a row to find that last used column--like if row 1 contains headers?

If yes:

Option Explicit
Sub testme()

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

With Worksheets("sheet1")
'I used row 1 to find the last used column.
ThisCol = .cells(1, .columns.count).end(xltoleft).column + 1
FirstRow = 2 'headers in row 1???
'and I used column A to find the last used row.
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(.Cells(FirstRow, ThisCol), .Cells(LastRow, ThisCol)).FormulaR1C1 _
= "=SUM(RC5:RC[-1])"
End With

End Sub

=sum(rc5:rc[-1]) in R1C1 notation
RC5 is the same row, column 5 (E)
RC[-1] is the same row, one column to the left of the cell with the formula



BSII wrote:

Hi Dave,

Thanks for the help. Essentially, I have a row of numbers always starting
in column E and with varying lengths. For example, on one sheet I might have
data in E9-L9 and I want to put the sum into M9 (right after the last set of
data). So, M9 = sum(E9:L9).

Another sheet might have data in E30-U30 and the formula would go into V30
(V30-Sum(E30:U30).

Any other thoughts?
BSII (Mike Lindauer)

"Dave Peterson" wrote:

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


--

Dave Peterson