PDA

View Full Version : Solved: Help w/ Summing Range



vassili
07-25-2007, 01:26 AM
i need to sum a bunch of ranges in a worksheet. the worksheet will contain many "pages" of an invoice. how many pages depend on user input.

i'm trying to get tSum to return a value to 2 decimal places. what i have coded does not work. any ideas?

also, there will be times when the code reads in blank cells (because of the blank space in between seperate pages of the invoice) or read in strings (because the stated range also includes the heading "amount" on every page). however, i think the val function takes care of these as both possibilites will return 0.


Sub getTotal()
Dim sumTotal As String
Dim i As Long
Dim Tpg As Long
Dim j As Long
Dim r As Long
Dim lRow As Long
Dim tSum As Long

tSum = 0

Cells.Find("Total :").Select
lRow = ActiveCell.Row
sumTotal = ActiveCell.Offset(0, 1).Address

'1st page has 62 rows of data, rest of sheets have 68 rows. Tpg counts total pages for the invoice to be used later on for something else.
Tpg = ((lRow - 62) / 68) + 1

For i = 14 To lRow - 1

Do Until Range("L" & i) <> ""
i = i + 1
Loop

Range("L" & i).Select

'this doesn't give the desired result of tSum returning a value to 2 decimal places
tSum = Format(tSum + Val(Cells(i, "L")), "#,##0.00")

'the reason for incrementing i before the "next i" is because the data resides in 2 merged rows, thus i need to incement by 2
i = i + 1

Range("L" & i).Select
Next i

Range(sumTotal).Formula = tSum
End Sub

Bob Phillips
07-25-2007, 01:34 AM
I am not really sure what you are doing, a sample workbook would help, but maybe this solves it



tSum = Round(tSum + Val(Cells(i, "L")), 2)

rory
07-25-2007, 02:45 AM
You have declared tSum as a Long integer so you will never get any decimal places. Declare it as a Double.
HTH
Rory

vassili
07-25-2007, 08:05 PM
You have declared tSum as a Long integer so you will never get any decimal places. Declare it as a Double.
HTH
Rory

yes, this works, thanks.