Sum in Excel - VBA 2

[cariengon]

For some reason, we can't get this to work.


'add total for Pmt Reg Total column

sum_row = last_row - 1
total_cell = Cells(last_row, 21)

total_cell = WorksheetFunction.Sum(sum_row, first_row)

What we're trying to do is add the column (U, which is column 21) between the fields that have been defined as the first row and last row. Is the last piece of code correct?

Thanks in advance!
Carie

 

[VulcanJedi]

sum = 0
For x = first_row to last_row
sum = sum + activesheet.cells(x, YOUR COLUMN HERE)
next x

OR

activesheet.cells(last_row+1, YOUR COLUMN HERE)= "=sum(first cell:last cell)"

Wouldn't this work?

 

[dwilson01]

Carie,

The worksheet functions work using ranges for the arguments, therefore, you have to define teh range you want to sum. If the data is in column 21 (Q) then you could use the following:

To enter a formula in the cell use:

ActiveSheet.UsedRange
tRow = ActiveSheet.UsedRange.Rows.Count
myRng = "U1:U" & tRow
Cells(tRow + 1, 21).Formula = "=sum(" & myRng & ")"

To enter the value in the cell use:

ActiveSheet.UsedRange
tRow = ActiveSheet.UsedRange.Rows.Count
myRng = "U1:U" & tRow
Cells(tRow + 1, 21) = Application.WorksheetFunction.Sum(Range(myRng))

Or you could loop through each cells as Tranpkp indicated above.

 

[VulcanJedi]

never heard of the usedrange property before, that is fantastic! thx Tranpkp
************************************
- Let me know if this helped/worked!

 

[cariengon]

The Usedrange works great, as long as you use it immediately before the code that you need to use it in. I actually had to delete that part and define trow as a fixed range.

But the myRng was exactly what I was needing!

Thanks!
Carie

 

[VulcanJedi]

I haven't really used it yet, just quickly, why much you use it prior? Why delete? Tranpkp
************************************
- Let me know if this helped/worked!

 

[xlbo]

'Cos usedrange CAN be incorrect - especially if data has been CLEARED rather then DELETED - excel gets confused and thinks data is still there. The usedrange will reset on a file save or if you access the object by using
usedrange.rows or similar
Therefore, you must access the usedrange object BEFORE trying to use any value associated with it
HTH Rgds
~Geoff~

 

[dwilson01]

You could always use the code that takes you to the last row in a worksheet (65536) and use

Selection.End(xlUp).Select

. In other words, for this problem

Cells(65536,21).Select
Selection.End(xlUp).Select
tRow = Selection.Row

This method doesn't always work when you have non-contiguous data and you try to perform certain operations. In this case it may work fine.

 

[xlbo]

Check this FAQ for a few ways to find the last cell:
faq707-2115 Rgds
~Geoff~