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
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