Solved: summing range

[satyen]

I am trying to sum data within two ranges. It keeps coming up with Object Defined error

ActiveCell.Resize(, vCol).Formula = "=SUM(" & vRow & t.Row - 2 & ")"

Please can someone help

Many Thanks!

[mdmackillop]

Try
ActiveCell.Resize(, vCol).Formula = "SUM(" & vRow & t.Row - 2 & ")"

You'll see the formula without the = sign, and it should show what is missing. There should be a colon in there somewhere.

[Bob Phillips]

Maybe



ActiveCell.Resize(, vCol).Formula = "=SUM(" & vRow & ":" & t.Row - 2 & ")"

[satyen]

you need the = sign mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87).

I think I will post the whole piece of code a bit later as its not working as I would like it to.

[mdmackillop]

The purpose in omitting = is to show the error in the formula.

[SamT]

vRow t.Row
What's up with the mismatched dots?

[Bob Phillips]

The purpose in omitting = is to show the error in the formula.

:)

[satyen]

ActiveCell.Resize(, vCol - 2).Formula = "=SUM(" & vRow & ":" & t.Row & ")"

This does not come up with an error now but doesn't sum a line of data, instead the range in all 15 columns. I need to add a column

would it be something like "B2:vRow & ":" & t.Row & ")"

[satyen]

actually something like this as I want it to start from vRow, but this doesn't work
ActiveCell.Resize(, vCol - 2).Formula = "=SUM(B":vRow " & ":" & t.Row & ")"

[Bob Phillips]

Maybe



ActiveCell.Resize(, vCol - 2).Formula = "=SUM(B":vRow " & ":T" & vRow & ")"

[satyen]

expected end of statement error with this one

[Bob Phillips]

Oops I copied your typo



ctiveCell.Resize(, vCol - 2).Formula = "=SUM(B":" & vRow " & ":T" & vRow & ")"

[satyen]

expected line no. or statement or end of statement error

[SamT]

ActiveCell.Resize(, vCol - 2).Formula = "=SUM(B" & vRow " & ":T" & vRow & ")"

[Bob Phillips]

I'm having a bad day



ActiveCell.Resize(, vCol - 2).Formula = "=SUM(B" & vRow & ":T" & vRow & ")"

[satyen]

its not quite doing what I want it to do..
its summing a whole row than a column. I want to sum from top to bottom vrow down to t.row

[SamT]

ActiveCell.Resize(, vCol - 2).Formula = "=SUM(B" & vRow & ":B" & tRow & ")"

[satyen]

Here is the code in it’s entirety, may be it may be easier to understand with the whole piece of code. I’ve commented some lines to make easier to understand the problem.

Sub mTest()

Sheets("All").Activate
'Totaling
Dim vCol As Long
Dim vRow As Long
Dim t As Range

vCol = Cells(3, Columns.Count).End(xlToLeft).Column '3=Start at First column heading row
vRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells.Find(What:="Calls", After:=ActiveCell, LookIn _
:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).End(xlDown).Offset(3, 0).Activate 'this will find where to insert "Sub Totals:"

ActiveCell.Formula = "Sub Totals:"
Selection.Font.Bold = True
ActiveCell.Offset(0, 1).Activate

Set t = Columns(1).Find("Calls:").Offset(1, 1) 'everything from here should be totaled uptil vRow and then copied accross
ActiveCell.Resize(, vCol - 2).Formula = "=SUM(B" & vRow & ":B" & t & ")"
Range("A1").Select
End Sub

[Bob Phillips]

Still guessing



Sub mTest()

Sheets("All").Activate
'Totaling
Dim vCol As Long
Dim vRow As Long
Dim t As Range

vCol = Cells(3, Columns.Count).End(xlToLeft).Column '3=Start at First column heading row
vRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells.Find(What:="Calls", After:=ActiveCell, LookIn _
:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).End(xlDown).Offset(3, 0).Activate 'this will find where to insert "Sub Totals:"

ActiveCell.Formula = "Sub Totals:"
Selection.Font.Bold = True
ActiveCell.Offset(0, 1).Activate

Set t = Columns(1).Find("Calls:").Offset(1, 1) 'everything from here should be totaled uptil vRow and then copied accross
ActiveCell.Resize(, vCol - 2).Formula = "=SUM(B" & vRow & ":B" & t.Row & ")"
Range("A1").Select

End Sub

[satyen]

yay! this worked! thank you soo much for your effort!

[SamT]

.

[satyen]

thank you all for your effort on this.